need help displaying record in listbox based on parameter selection in combobox!! (1 Viewer)

[killyridols]

Hello,
Basically I want to perform a parameter query with a drop down menu. To do this I have created a form with a combo box (combo0) and a listbox (list3). The combo box is a drop down menu of sample_id (from my query ANART_pt2) I want the listbox to display the full record from ANART_pt2 for the sample_id selected in the combobox.

In the properties of the listbox, for row source, I have:

SELECT ANART_pt2.sample_id, ANART_pt2.lat, ANART_pt2.long, ANART_pt2.maxi, ANART_pt2.waterdepth
FROM ANART_pt2
WHERE (((ANART_pt2.sample_id)=[forms]![ANARTtest].[combo0]))
ORDER BY ANART_pt2.sample_id;

With the form open, if I select the desired sample_id, nothing shows up in the list box. However, if I right click in the listbox and open properties, select the query builder for row source, then close the query builder and the properties menu, the desired record shows up!

So, it is working, but there must be something small that I am missing! Perhaps something in the properties?

Does anyone have any ideas?
Thank you!

 

[pbaldy]

Try requerying the listbox in the after update event of the combo.

 

[killyridols]

OK, so I tried typing
Me!Combo0.Requery
into the After Update Event in the combobox properties, and I got this error:

The Expression After Update you entered as the event property setting producted the following error: The object doesn't contain
the Automation Object 'Me'.
*The expressiong may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
*There may have been an error evaluation the function, event, or macro

I am clearly doing something wrong...I apologize, I'm quite new to this. Could you further assist me?

 

[Scooterbug]

Try using me.listbox.requery

 

[killyridols]

nah... same error!

Although, when I type the command directly into the after update field, then reopen the form select my sample_id I get the error message

"MS Access can't find the macro 'me'."
I guess it assumes I'm building a macro.

But, if I click the "..." and select "Expression builder" and enter the command, that's when I get the error message that I posted above..

Anyways, is there any other way of entering the requery command for the combobox??

 

[pbaldy]

Are you entering directly in the properties window? That's intended to be in VBA, so it should look like:

Private Sub...
Me.ListBoxName.Requery
End Sub

 

[Kryst51]

How about this? It has worked for me before.

DoCmd.requery "LISTBOXNAMEHERE"

 

[Scooterbug]

How about this? It has worked for me before.

DoCmd.requery "LISTBOXNAMEHERE"

He's putting that line into the field for the After Update event. as Paul said, he needs to go and put it in as code.

Also Killy, you need to requery the listbox, not the combo box. From what you stated, the data in the list box is dependant upon the selection of the combo box, correct?

 

[Kryst51]

He's putting that line into the field for the After Update event. as Paul said, he needs to go and put it in as code.

Hey, I didn't see Paul's response until I posted my reply, sorry.

 

[Scooterbug]

hehe, no problem...answers are flying fast today it seems

 

[killyridols]

yaaaaaaaay!!!!!!
Success!
So, it wasn't working at first, because I didn't realize that when I was creating an event, for whatever reason a before update event would appear automatically...
So I deleted that, and put in my after update event "me.list3.requery"
and it worked..
Thank you so much!

 

[Scooterbug]

hehe, no problem...answers are flying fast today it seems

 

[killyridols]

Hi!
Me again...
So I was using Access 2002 yesterday when I was attempting this, and was in the end successful.
However, today, I tried to recreate the same thing in Access 2007, following the exact same steps, and I'm running into the same problem as yesterday. The requery event does not seem to be working...
Would their be a syntax difference between 2002 and 2007 in visual basic??
Thank you

 

[pbaldy]

No, but code is disabled by default in 2007. First item under configuration:

http://allenbrowne.com/Access2007.html

 

[killyridols]

tried what they suggested there, and no changes....

 

[pbaldy]

What exactly is your code, where is it, and what's happening (error, etc)? There is no change in this type of thing between versions.

 

[killyridols]

OK,
for my combobox (combo0) , my row source is :
SELECT ANART_pt2.sample_id
FROM ANART_pt2
ORDER BY ANART_pt2.sample_id;

My After Update Event is :
Private Sub Combo0_AfterUpdate()
Me.List2.Requery
End Sub

My listbox (List2), has row source:
SELECT ANART_pt2.sample_id, ANART_pt2.lat, ANART_pt2.long, ANART_pt2.maxi, ANART_pt2.waterdepth
FROM ANART_pt2
WHERE (((ANART_pt2.sample_id)=[forms]![Form1].[Combo0]));



What I have here runs perfect in Office 2002, but when I try it in Office 2007, when I select a sample id from the combobox drop down menu, no records show up in the listbox. BUT, when i open the properties of the listbox, and then open the row source information, then close both windows, the proper record shows up in my listbox.

 

[pbaldy]

Can you post the 2007 db?

 

[JediYodaNT]

Ok, I'm a little turned around here. I am trying to do a similar thing here, but lack the training to know how best to do this. I thought I found a way, but it's not working. Reading this thread has me even more turned around.

Situation: I have a form that I am working on. One of the fields is a combo box that lists the associate names from T_Associate. This table has three fields ID, Name, and a lookup column for Site which references T_Site. I'm trying to build a field on the form that will auto populate the Site field on the form with correct site, dependant on the associate name selected.

I'm sure this is simpler than I'm making it, but I can't seem to figure it out. Am I creating an After Event that assigns the value to a field, or am I setting a query on the SiteBox that tells it to look for a value dependant on the selected value in the AssociateCombo?

Any help would be greatly appreciated.

 

[Scooterbug]

Jedi,
I am assuming that the lookup is not at table level. If it is, you should get rid of it. Search the forums for lookup at table level for reasons why.

Now, if it's not, your problem is easy to solve. Go into the properties of the combo box, and under Row Source, click on the button that has the three dots. This should bring you into the Query Builder. Simply add the table that has the site information and make sure that the join is on the fields that relate between T_associate and T_sites. Put the field with the site name from T_site into the query. Close out.

Now in the properties for the combo box, increase the Column count by 1 and add a ,0" to the Column Widths. In the After Update event for the combo box, add the following:

me.txtSiteName = me.combobox.column

n will equal the last column, which will be the total column count -1 (the count for columns starts at 0). Substitue the control names with the controls on your form.