help with a form

[Brian Foshee]

Ok got my earlier issue solved now a new one that is unrelated has popped up. I have a database with two tables

tblAssets
fields are as follows
PC Name
Serial
Make
Model
User
Department


Location

tblUserHistory
fields are as follows
Previous Users
PC Name

My form shows all the information from the Assets table. What I want to do is have a list on my form populate based off of the PC Name field on the form that lists all the Previous Users from the UserHistory Table. IE if I am looking at the record for PC001 then I want this list to show all of its previous users if any.

[TheShabz]

have the record source of the listbox be the query
SELECT [Previous Users] FROM tblUserHistory WHERE [PC Name] = Forms!myForm!controlName

replace "myForm" with the name of the form and "controlName" with whatever holds the PC Name

Also, you may have to do a Me.Requery in the afterUpdate for whatever holds the PC Name.

[Brian Foshee]

would you put that in the VB editor or where would you put that sql statement?

[TheShabz]

if you open the properties dialog of the listbox, itll have an option for "record source"

[Brian Foshee]

found it gonna give it a try and post back

[Brian Foshee]

nothing is populating in my list box

[TheShabz]

throw the query in the SQL view of a blank query and see if it returns the desired results. If it does, I dont know what's wrong.

[Brian Foshee]

Ill give it a try Monday when I get back to work.

[Brian Foshee]

query works if i just throw it in sql and run it. Cant seem to get it to populate the list box though.

[TheShabz]

Alright. Save the query. Create a new listbox and use the wizard that pops up to select the query as its source. That should do it it.

[Brian Foshee]

thanks got it working had to throw some Me!lstUserHistory.Requery with the search and clear buttons. one more issue is when I navigate through records using the built in buttons at the bottom of the form it does not refresh the data in the list box. Any idea?

[TheShabz]

Does the navigating through records change the value of PC Name? If so, you can probably include Requery you had above in the AfterUpdate section. If not, then I'm not sure what you mean or want happening.

[Brian Foshee]

yea I tried throwing that in there. pressing f9 refreshes it and clears the list box but just clicking through records doesnt

[TheShabz]

did you try a generic Me.Requery instead of the specific control? Also throw a Me.Refresh in there and see if it works. Outside of that, I'm afraid I've stepped over my knowledge boundary.

[Brian Foshee]

I just solved it thanks for the suggestions. I put the Me!lstUserHistory.Requery on the current action of the main form.