Requery value of combo to listbox


,

I have a form with a text box, a combo and a listbox.

The textbox stores the name of the user.

The combobox gets its data from a query.

I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. records under that criteria, will have to show up in the list box.

Posted On: Monday 22nd of October 2012 05:31:09 AM Total Views:  528
View Complete with Replies

RELATED TOPICS OF Microsoft Access PROGRAMMING LANGUAGE




Automatically update/refresh/requery a subform

, I have a form and on this form there sit two subforms. Subform1 Subform2 The main form and the two subforms are all linked via parent/child relationships. So that when the Data in the main form is changed, the relevant data is shown in the two subforms for the same Site. Now, I have a Button in Subform1. When this button is clicked it updates a set of data to a column in Table1. Subform2 is there to show this updated data from Table1. However, currently it wont refresh or requery automatically. I have to go to another record and come back for the fresh data to show up. What I want is as soon as the button is clicked on Subform1, Subform2 should update instantly. How can I do that I tried on the ONClick even of the button to put this: If CurrentProject.Forms("Subform2").IsLoaded Then Forms![Subform2].Requery End If That didnt work I also tried on the OnClick Event to put this: DoCmd.RunCommand acCmdSaveRecord That didnt work. Please help.
VIEWS ON THIS POST

298

Posted on:

Wednesday 17th October 2012
View Replies!

requery on exit w no code

I have a strange problem. On some forms with listboxes, when I close (via a close button) the listboxes want to requery after the form closes prompting a parameter box to pop up. I have no code inserted to do this. If I file-close, it works fine. However some users don't report this to happen on their PC's. are supposed to be set up the same though with me as an exception (having it fully installed). I tried converting it to 2000 then back to 2002 (after trying compact-repair) which did fix it for me opening holding the shift key, but not if I open it like else. We just got new PC's running XP pro from 2000. Access version is 2002 and did not change. Any ideas Our internal programmer is stumped as am I. He just says "that's why Access shouldn't be used for anything". I develop basic apps in Access and he hates it so I have to make sure it's perfect before rolling things out or I'll be back to the stone ages.
VIEWS ON THIS POST

208

Posted on:

Thursday 18th October 2012
View Replies!

User is forced to first record upon requery after deleting a record

I have the following code in a form which will delete a record from a table; however, upon the requery it takes the user to the first record of the form. How can I capture and move the user to the next record if one exists (or last record if one doesn't) instead of making them move to the first record Code: DoCmd.RunSQL "DELETE FROM [mytable] WHERE [recordID] = " & Me.recordID Me.Requery
VIEWS ON THIS POST

189

Posted on:

Monday 22nd October 2012
View Replies!

Insert null values for date in ms access 2003

how to allow null value to be added into database (access 2003) The date changed to 11/30/1999 whenever there is null.
VIEWS ON THIS POST

199

Posted on:

Monday 22nd October 2012
View Replies!

Setting Default value in a control as Date makes each new record show a Time....

I have a table named "tiny" with 2 columns, IID (a number) and date. The date has a date in format mm/dd/yyyy. I have a Form with a subform. The subform is pointed to another table - TMPtable and displays the tmptable in datasheet view. The tmp table has no records in it. I want to assign the default vaule of the field [Driver Test Date] in the subform to the date in my "tiny" table. The Code I have is as follows: Code: Private Sub Form_Load() Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("tiny") rst.MoveFirst [Driving Test Date].DefaultValue = rst![Date] rst.Close End Sub What happens is the default value is assigned a time instead of the date.... If I do the following: Code: Private Sub Form_Load() Dim rst As Recordset Set rst = CurrentDb.OpenRecordset("tiny") rst.MoveFirst [Driving Test Date]= rst![Date] rst.Close End Sub it will assign the value from tiny table to the first record (it gets the date I want); however, the default value (which I can see in the buffer record below it on the table, the record that will be created) is a time....and when you try to create a new record, lo-and-behold it enters the time instead of the date. I've tried setting the format to short date and everything.... How can I get the recordset I open to assign the default value in [Driving Test Date] properly
VIEWS ON THIS POST

140

Posted on:

Monday 22nd October 2012
View Replies!

Get rid of "Sticky" value in textbox on form??

Hi all, I've got a textbox on a form that has its value calculated using a function. The function is working well but the problem is that this value "sticks" in the textbox and does not disappear even when browsing through other records in the form. is there any way I can get rid of this "sticky" value\t
VIEWS ON THIS POST

146

Posted on:

Monday 22nd October 2012
View Replies!

List box row source dependent on another list box value

to all! in mi data base I have the following tables: 1. [Clients] table with the fields: - [ID Client] (PK) connected to [Sold] Table - [Name] 2. [Sold] table with the fields: - [ID Client] connected from [Clients] Table - [ID Item] connected from [Items] Table 3. [Items] table with the fields: - [ID Item] (PK) connected to [Sold] Table - [Item] - [Price] - [Type] connected from [Types] Table 4. [Types] table with the field: - [Type] (PK) connected to [Items] Table relationships are one-to-many. im making a form for updating sold items. because the [Items] table is very long, I have a list box with the row source [Types]; what I need is that once a value is selected in that list box - another list box will show only the [items] matching the value on the [Types] list box. how can i make this kind of connection between list boxes or any other way to divide the [Items] Into their types for the user to select. if its not possible how can I prevent from the user the need for scrolling threw very long list boxes and not typing the value (combo box)
VIEWS ON THIS POST

319

Posted on:

Monday 22nd October 2012
View Replies!

Passing values to queries by form

In my Coin Inventory database there is a category of books that each coin can be housed in. One coin will be stored in one of the books. I created a form (frmSelectFolder) that has a list box (lstSelectFolder) that the user makes a selection to determine which book they want to edit or view. The command button to call the form is disabled so the user is forced to either exit the form frmSelectFolder or make a selection from the list box. Once the user makes a selection the command button is enabled allowing the user to click it. I am calling the form frmSelectFolder from a menu (its a popup box) and the form closes after the command button is clicked. This is what I have working now. What I want to happen is that the command button opens up a form (frmFolderEdit) and the value of StorageID (from the query qryStorage underpinning the form frmSelectFolder) is passed to the frmFolderEdits query qryFolderEdit.. How do I go about doing this
VIEWS ON THIS POST

189

Posted on:

Monday 22nd October 2012
View Replies!

I need to show the sum value on the textbox

, I have a table which is called "clients_data". It contains the following information: Id|Name|Surname|Mobile|Expenses|Date| I can generate reports based on Names, Surnames, Mobiles, filters on Expenses and filters on Date which looks like this: Id|Name|Surname|Mobile|Expenses|Date| 1 |aa |zzz |111 |50$ |18-Jan-09 2 |bb |xxx |222 |30$ |19-Jan-09 3 |cc |yyy |333 |40$ |20-Jan-09 What I exactly need to do is that when I generate this report I need also the sum of Expenses displayed in a Textbox (in this case 120$). Could someone help me to resolve this problem
VIEWS ON THIS POST

224

Posted on:

Monday 22nd October 2012
View Replies!

Howto Stop query from asking for value?

I have a query Ive made that does some math with current values. Fields: Month, Delay Min Before, Delay Min After, Delay Min Saved, Percent: Round((([SumOfDelay Min Before]-[SumOfDelay Min After])/[SumOfDelay Min Before])*100,1) The last one is to get a percent saved from Before and After. My issus is when I run this query it will ask for "SumofDelay Min Before" and again for "SumOfDelay Min After". If I just click thru both and the query will output the correct numbers for me. From this query I made a sub from. The trouble is when I open the form for use it asks me every time for both values when the refresh button is hit. Im thinking Ill have to make a 2nd query with the last field to get this to work correctly. Sound right Edit: more info The query has "totals" clicked. Month is Group by, Before/After/Saved are all SUM, Percent is Group by.
VIEWS ON THIS POST

203

Posted on:

Monday 22nd October 2012
View Replies!

Testing values

I have two textboxes on a form. I want to test to see if they have values. If text1 has a value write it to a variableX If text1 does not have a variable then test text2 If text2 has a variable then write it to a variableX If text2 does not have a variable then clear the variable. Any thoughts on how to do this THanks
VIEWS ON THIS POST

182

Posted on:

Monday 22nd October 2012
View Replies!

Getting max value

I am trying to get he max value from a table that I will be updating in a form... I am using this max value as the unique identifer. I was doing this in Access 2003 but now in Access 2007...its not working... I am trying these two ways...maybe someone can shed some light on this\t\t The table is a LINKED SQL Table in SQL Server Hope someone can help here... THAnks EXAMPLE 1 In this example. Complaint Field Name dbo_Complaints_Table Linked SQL table name Complaint is the Textbox name in the form ERROR: Run time error '6' OVERFLOW Code: 'populate ID box with largest value + 1 Dim varID As Integer varID = Val(DMax("Complaint", "dbo_Complaints_Table")) + 1 Complaint = varID Me.Refresh EXAMPLE 2 I am getting errors on the ADODB Dim statement Code: Dim rs As ADODB.Recordset, MyVal As Currency Set rs = New ADODB.Recordset Open a Connection to the Recordset rs.Open "SELECT MAX(Complaint) from dbo_Complaints_Table", CurrentProject.Connection rs.MoveFirst MyVal = rs.Fields(0).Value Me.Text1.Value = Format(MyVal, "#,##0.00") MsgBox MyVal rs.Close Set rs = Nothing
VIEWS ON THIS POST

231

Posted on:

Monday 22nd October 2012
View Replies!

AfterUpdate - values not calculating

Below is the code I have created to calculate the values "CalcValue" and "CalcWork". The values for AssignValue, AssignPriority, Complex, Effort, Goal are all assigned by the selection made in the referred to/related combo boxes. However, the caluclated values return the concatonation of the intergers and not the sum of the integers. Additionally, if I am to use the CDec() function how do I specify the number of decimal places what am I missing here thanks! Private Sub cboAssignPriority_AfterUpdate() Dim CalcValue, AssignValue, AssignPriority, Complex, Effort, Goal, CalcWork As Integer Value = 0 AssignValue = 0 AssignPriority = 0 CalcValue = 0 CalcWork = 0 AssignValue = Me!cboAssignValue.Column(3) AssignPriority = Me!cboAssignPriority.Column(2) Complex = cboDBObjectID.Column(2) Effort = cboTaskTypeID.Column(3) Goal = cboAgencyGoalID.Column(2) Value = AssignValue + AssignPriority CalcWork = Complex + Effort + Goal CalcValue = CDec(Value) CalcValue = Me!lngCalPriority Debug.Print "Complex="; cboDBObjectID.Column(2) Debug.Print "Effort="; cboTaskTypeID.Column(3) Debug.Print "Goal="; cboAgencyGoalID.Column(2) Debug.Print "AssignValue.Column(3)="; cboAssignValue.Column(3) Debug.Print "AssignPriority.Column(2)="; cboAssignPriority.Column(2) Debug.Print "Value ="; Value Debug.Print "CalcValue ="; CalcValue Debug.Print "CalcWork ="; CalcWork End Sub , thanks - I think that's what I've done (almost) but... CalcValue won't calculate correctly! I don't get it. jh Originally Posted by sbenj69 Try something like this: Code: Private Sub cboAssignPriority_AfterUpdate() Dim MyValue as Single Dim CalcValue as Single Dim AssignValue as Single Dim AssignPriority as Single Dim Complex as Single Dim Effort as Single Dim Goal as Single Dim CalcWork as Single MyValue = 0 AssignValue = 0 AssignPriority = 0 CalcValue = 0 CalcWork = 0 AssignValue = Val(Me!cboAssignValue.Column(3)) AssignPriority = Val(Me!cboAssignPriority.Column(2)) Complex = Val(cboDBObjectID.Column(2)) Effort = Val(cboTaskTypeID.Column(3)) Goal = Val(cboAgencyGoalID.Column(2)) MyValue = AssignValue + AssignPriority CalcWork = Complex + Effort + Goal CalcValue = Format(MyValue,"0.00") CalcValue = Val(Me!lngCalPriority) Debug.Print "Complex="; Val(cboDBObjectID.Column(2)) Debug.Print "Effort="; Val(cboTaskTypeID.Column(3)) Debug.Print "Goal="; Val(cboAgencyGoalID.Column(2)) Debug.Print "AssignValue.Column(3)="; Val(cboAssignValue.Column(3)) Debug.Print "AssignPriority.Column(2)="; Val(cboAssignPriority.Column(2)) Debug.Print "Value ="; MyValue Debug.Print "CalcValue ="; CalcValue Debug.Print "CalcWork ="; CalcWork End Sub Not sure if you meant for these lines to be like this: Code: CalcValue = Format(MyValue,"0.00") CalcValue = Val(Me!lngCalPriority) Not exactly sure if that's what you wanted, just went off the code you had posted.
VIEWS ON THIS POST

231

Posted on:

Monday 22nd October 2012
View Replies!

Maintain field value

I have a value in a form that has code behind it to calculate a value for that field when that field is "clicked." works as it should. However, if I go to look at a previous record in the form, and click on that cell, it calculates a new value, when it should maintain the value that is already there. Is there a way to "lock" in the values on a record so if a user goes back to look, the value cannot be changed
VIEWS ON THIS POST

177

Posted on:

Monday 22nd October 2012
View Replies!

Load record to form depending on value in combo box

, I have 2 combo boxes. The first one gives a lis of Prefix letters for railcars. The second uses the value of the 1st and finds railcars that start with the same letters. finally got the 2nd combo box to list the prefix and number of a railcar based on the 1st combo box and also got it to clear as each run-through starts. Now that the user can select a number (ex. dfcv1234 as the railcar), I need to find the record with the prefix dfcv and the car number 1234 and load the record to the form for view and editing. So far, I know it needs to be the after update event and have a select statement that finds the [key], [prefix], [car_number] that matches the value selected on the form but how do I use the key to make the record show on the form
VIEWS ON THIS POST

360

Posted on:

Monday 22nd October 2012
View Replies!

Text box value is null

I have a very simple question that I should now the answer to but I must be having a brain cramp.I have a text box on my form and I want to make sure the textbox is not null before running some code. How do I do that if Me.txtBatchID Is Null -I get an error 'Object Required' If Me.txtBatchID = "" -doesn't work.Even if there's nothing in the text box, apparently access thinks it is. Can anyone help me with this I can't believe I haven't been able to figure this one out! GEM
VIEWS ON THIS POST

163

Posted on:

Monday 22nd October 2012
View Replies!

IIf Statements and NULL values

Within my query I have a field that I need to do a nested IIf Statement that has a NULL value. The name of the field is "smokingcessation". The possible responses are a "1" = Yes, "2"= No, "3"=Unknown and there is also the possibility of a "no answer" at all (blank). I've got the following statement to work so far but need to include the possibility of a blank/no answer response. My statement so far is as follows and is working: IIf([smokingcessation]=1,"Yes",IIf([smokingcessation]=2,"No","Unknown")) How would I do a statement that would take into account the existing possibilities plus that of a blank (NULL) answer and leave the field blank in that case In otherwords, I need to see a "Yes", "No", "Unknown" or a blank field.
VIEWS ON THIS POST

224

Posted on:

Monday 22nd October 2012
View Replies!

Retaining a value entered throughtout the user's session

I am creating a database to track a student's course load in a degree program. When the student enteres their ID on the first form I want to retain that value so subsequent forms and views display their records, or allow new entries/updates to different tables without re-entering their ID. I'm having no luck passing the value entered into the first form's text box to other processes selected prior to closing out the database. Thank you.
VIEWS ON THIS POST

206

Posted on:

Monday 22nd October 2012
View Replies!

Lookup value from a table to input in a query

I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions Thank you! KellyJo
VIEWS ON THIS POST

485

Posted on:

Monday 22nd October 2012
View Replies!

Automatically update value in the subform from Crossstab query

, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
VIEWS ON THIS POST

593

Posted on:

Monday 22nd October 2012
View Replies!