Combo values from query based on form fields

I am setting the values for a combo box in a form(s) via a query that 
'filters' the results with criteria based upon the values of other fields on 
the form. The combo is a field that is bound. However, this is giving all 
kinds of problems ranging from Access completely crashing to being asked for 
the parameter values of those criteria fields when closing the form. I have 
tried making the combo an unbound field and then setting the value of the 
bound field to that unbound field after update, but that still leads to the 
same issues. How can I do this?

As example - I have a form with ClientID, which the user selects and then 
based upon that I will populate the values of a combo box for events which 
selects all events attended by that client for which there is a charge. The 
user should then select the relevant event and the event number is then 
stored in the table underlying the form (which is recording payments made). 

The Row Select for the field Event is  SELECT Event.EventID, [EventTitle] & 
", " & [EventDate] AS EventTitleDate FROM Clients INNER JOIN (Event INNER 
JOIN EventAttendee ON Event.EventID=EventAttendee.EventId) ON 
Clients.ClientID=EventAttendee.ClientID WHERE (((EventAttendee.Paid)=False) 
And ((Event.EventCharge)>0) And 

The bound column is 1.

(I have two other forms doing similar things, the more complex one is form 
with a continous sub-form which contains a combo with a query behind that 
uses two other fields from the subform - if I just enter the main form a few 
times in succession it crashes Access!)

Any help gratefully received!!

1/26/2008 10:21:00 AM
access.forms 6864 articles. 2 followers. Follow

0 Replies

Similar Articles

[PageSpeed] 18


Similar Artilces:

Automatically set the data type as date for a Make Table Query
I have a make table query that I run daily. One of the fields of the query is a date field. The source table for the query treats the information as a text string in a date format that is not recognized by access. I have created formula that parses the string to put it into standard "mm/dd/yy" format. However, when I run the make table query it treats the date field as text. This is a problem because I use a form to hold the criteria for a query that I run on the new table. In order to get the query to work, I have to manually set the data type to short date. This will ...

Compare values in columns
How can I compare a master part numbers list in one Excel column or worksheet against actual values in a second Excel column or worksheet, and display the missing part numbers that were not in the second column in a new column or worksheet? For Example: Part Number Master Part List Missing Parts 12A221315 12A221315 12A221332 12A221316 12A221316 12A221333 12A221317 12A221317 12A221318 12A221318 12A221319 12A221319 12A221320 12A221320 12A221321 12A221321 12A221322 12A221322 12A221323 12A221323 12A221324 12A221324 12A221325 12A221325 12A221326 12A221326 12A221327 12A22132...

Produce report from two queries from one table?
I am currently working upon a database which is being used to produce pricing qutoes. I have ran two queries to show item of products which have been quoted for and one which shows a sum of the remaing unquoted products. Each product is placed into different sections which relates to its main function, be it electrial or machineary. I am hoping to have a report which will allow me to show all quoted products under the sections, but have the unquoted products for the sections as one line entry in the report. I have ran a normal report showing full list details of both quoted and un-quo...

Filtering a combo box using a combo box
All, I am trying to filter the records from a table displayed in a combo box using a combo box on the same form. I built a query and set the criteria as Me![FormName]![Combo_box]. This allowed me to filter the list, but it did not do exactly what I wanted. When the first combo box is blank, the second one is also blank. Is there anyway I can get the second combo box to display all of the records when the first box is blank? Thank you for your help. Regards, John Try a criteria in the second combo of: Like Forms!YourFormName!Combo1Name & "*" -- Arvin Meyer, MCP, MVP http:/...

Linking Values in Worksheets
Is there a simple method of linking a column of values in one worksheet to another worksheet? I can do it manually, one at a time but is there a way of copying the link down the column? (The columns are identical in length) Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1369 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1370 Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C$1371 etc, etc Many thanks david Hi David try ='C:\Registers\[Stock Control-Sigma-SMTech.xls]Sheet1'!$C1369 and copy down Frank DavidM wrote: > Is there a simple method of linki...

Retrieving Textbox Values
Hi, I have a myriad of textboxes on my form, and from time to time, I need to retrieve some of their values in comma-separated format. The textboxes are named in the format of a letter and two digits, for easy identification, like A22 or B54 etc. Before a textbox is updated, I want a display of values in the adjoining boxes displayed to assist a user in making his decision on a new entry. This is the code that I tried on their got-focus event: 'If TypeOf Screen.ActiveControl Is TextBox Then If IsNull(Screen.ActiveControl) Then Dim X As Integer, P As Integer, ...

Field Type Change
I am trying to change a free text field to a drop down menu in my current database, that already has data entered for this particular field, in order to avoid multiple versions of the same organization that are just typed in differently. I'm assuming that I'll need to recode the different versions of an entry to make them uniform but after that, I'm unsure how to proceed so I won't lose any data. Is this possible or will I need to delete the field, recreate it as a drop down and then re-enter the data? Thanks in advance! Mike Thanks everyone for all the input and advice s...

Display Formula Values
I have a number of formulas that are displaying the result using the value that is displayed in a referenced cell rather than what was actually input. For example, if my formula in cell A1 is B1*C1 and my value in C1 is .075 and the cell is formatted to display a percentage with no decimal places my formula in B1 uses 8% instead of 7.5%. How can I address this across the entire workbook rather than having to change the format in each of the necessary cells. Thanks for your help. What formula do you have in B1? -- Regards, RD --------------------------------------------------------...

Combo Box on Forms
Hi, I am using a combo box on a form that has several items w/ one being Other. If the user selects other I want them to be able to input what they want so that it will go into the table instead of just the "Other". I just am not really sure on how to do that. Can you help? Thanks, Jaime In design view click on VIEW - Properties and the the combo. Then change Limit To List to No. -- KARL DEWEY Build a little - Test a little "" wrote: > Hi, > > I am using a combo box on a form that has several items w/ one being > Other. If the use...

Combo Boxes
Someone sent me an Excel document where each of the cells in I1:I312 are combo boxes. When you click off of those cells, the down arrow disappears. The list for the combo boxes are cells AA1 and AB1 (AA1 is New and AB1 is Existing). Please explain how this was done? Also, can you please tell me if it is possible to make New and Existing autocorrect entries. So even though the choices are New and Existing, if you hit N, New appears, and if you hit E, Existing appears. Thanks!! It sounds like Data|Validation. Take a look at Debra Dalgleish's site:

Table Design with Many Fields
I am using Access 2007. I have a table ACCOUNTS, with fields for account number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is sig...

2nd to last value in column
Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg Try the below to get the data from the cell right above that? ...

Query Help 03-14-08
Hi, I have a table with two field: LOGNumber and VINumber I would like to see all the records where the VINumber is duplicated for a particular LOGNumber. Can anyone help me please. Thanks in advance. Try something like: SELECT LOGNumber, VINumber, Count(*) FROM YourTableName GROUP BY LOGNumber, VINumber HAVING Count(*) > 1 To build this query through the graphical query builder, create a new query and add your table to it. Drag to two fields into the grid, then type the LOGNumber field into the grid a second time. Change the query into a Totals query (there's an icon with a...

Separator in combo box
hi, Is it possible to have a line separator in a combo box? I mean something like a separator in menus? thanks, Behzad Try :- -- Regards, Nish [VC++ MVP] /* MVP tips tricks and essays web site */ /* My blog on C++/CLI, MFC, Whidbey, CLR... */ "behzad" <> wrote in message news:%23fVB3em5EHA.3368@TK2MSFTNGP10.phx.gbl... > hi, > Is it possible to have a line separator in a combo box? I mean something > like...

area chart doesn't show values
hi all, in excel 2003 i have created a area chart, based on values per day. If i point the mouse on a data point, a flag will show the data and date of this value. after open in excel 2007, this will only work, if i change the chart type from area to line-graph, so do everybody know, how to show the values in an area chart? thanks and greetings from switzerland Dieter ...

Distribution form for Payables could be on main form
When I enter a payable transaction, it would be very handy to have the distribution form at he bottom of the screen just like the Miscellaneous Check in GP 9.0 Instead I need to go to the distribution screen, even if I only want to confirm that the accounts are correct. Many times I also want to put a memo on a distribution line. Along with this it would be handy to be able to set the default as to whether the extra lines on the distribution form are open or closed. I always have to open them to see the account discription and enter a memo. I did put this all on a macro key so I can g...

#N/A Values : Returned by Formulas vs Entered Manually
Hello; When some cells of the data series have #N/A values returned by formulas, the corresponding chart fails. But if the #N/A values are manually entered into those same cells, the chart works fine !! To my understanding, Excel Charts treat cells with #N/A values as empty cells, so one may select the relevant chart option to "leave gaps", which is perfect. With this apparent different interpretation (by Excel Chart) of the same #N/A values in the data series, how can I make the #N/A values returned by formulas acceptable by the chart ?? Thank you for your help. The #N/A ...

access compare values and select higher of two
In Access database I want to compare the values in two fields in a form and then select the higher value, insert it into another field and then use in a formula. E.g. Value 1 = 500 Value 2 = 600, 600 to be inserted into another field and then be multiplied. On 11 apr, 21:26, Captain Turtle <Captain> wrote: > In Access database I want to compare the values in two fields in a form a= nd > then select the higher value, insert it into another field and then use i= n a > formula. > > E.g. Value 1 =3D 500 =A0 Value 2 =3D 60...

Multiple Combo Boxes Highlighted
Hi, I have a problem with something in VB6. I have 4 combo boxes with individual names (not an array) located on an SSTab object. When I select another tab and then return to the tab these boxes are located on, each of them appears to be highlighted in blue. I add values to them only when loading the form so I'm at a loss as to why this happens ? Any ideas are greatly appreciated. Thanks, Jen. "Jennifer Ward" <> wrote in message news:eq5HrcBrKHA.6064@TK2MSFTNGP02.phx.gbl... > Hi, > > I have a problem with someth...

replacing values in vba
Hi, i have a problem with one of my column, i want to get rid of #N/D! and 0 values, but leave the rest. I have already coverted #N/D! to text format, but following piece of vba doesn't work. Whats wrong with it? TIA Maciek Sub test2() Dim iLastRow As Long With ActiveSheet iLastRow = .Cells(.Rows.count, "A").End(xlUp).Row For i = 2 To iLastRow If .Cells(i, "V").Value = "#N/D!" Then .Cells(i, "V").Value = "" ElseIf .Cells(i, "V").Value = "0" Then ...

Query based on field in form, not working in 2007
Hi After upgrading to Access 2007 (from 2003), I have a query that has stopped working. It is something like SECELT id FROM [tbl names] WHERE ([tbl names].firstname = [forms]![My form][firstname]); It does not seem to register the value of the "firstname" textbox in the form [My Form]. Accordign to the SQL profiler, the query just sends an empty field "". Therefore instead of searching for a specific entry, I get all my entries... The query seemed to work fine in Access 2002 and 2003. When using WHERE ([tbl names].firstname LIKE "*" & [forms]![My for...

Tranform Pivot Query?
I hv 2 tables with structure as below: [Empl] ID Name 1 Mr A 2 Mr B 3 Mr C 4 Mr D 5 Mr E [Empl_Tran] TrID Empl_ID Date Code 1 1 10/11/2009 SL 2 1 10/11/2009 TR 3 1 11/11/2009 W 4 1 12/11/2009 W 5 1 12/11/2009 TR 6 2 10/11/2009 SL 7 2 10/11/2009 TR 8 2 11/11/2009 W 9 2 12/11/2009 W 10 2 12/11/2009 TR 11 3 10/11/2009 SL 12 3 10/11...

#VALUE! #3
I want A1 to report an error that is in A6 but my formula doesn't work. A6 reads: #VALUE! The formula A1 is =IF(AN6=#VALUE!,"You messed up","") Help. Try =IF(ISERROR(AN6),"You messed up","") -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions "Pencil" <> wrote in message news:Xns981091707C316pencilunlistedcom@ >I want A1 to report an error that is in A6 but my formula doesn't work. > A6 reads: #VALUE! > The formula A1 is =IF(AN6=...

Solution To Microsoft Knowledge Base Article
Save the distribution list as a text file and then import the text file either into excel or back into outlook in the contacts folder. From there, you can either drag the lists' member information into a message or print out the list. I discovered this solution when trying to automate the creation of a separate contact card for each member in the list. By saving the list as a text file and then importing the file into my contacts folder, I was able to create separate cards for every member of the list. rgds//Jerome ...

HELP !! Combo box to feed other combo boxes
Hi, I have a form with a stock list. One field is [Dealer Allocated] so stock can be allocated to a specific dealer - this is a combo box (combo1) for single allocations. I now also want to be able to update this combo box for several records in bulk. I thought I would do this by having a second combo box in the footer of the form which the user selects the dealer from and then clicks a button which looks for first record without dealer allocated and updates the value of combo1 to the value of combo2. This would then be enclosed in a Do Loop for specified number of records. I can...