access program not working properly! (1 Viewer)

hey guys,
i really hope one of u guys can help me, I'm pretty desperate. I'm by no means an expert in access. other then then occaional class project, I have no experience. A few months back I i started to built a database to hold client information for a internship i worked @. I had gotten pretty far with it. The i stopped the internship for 6 months and now im back and they are running a newer version of access. I took the database home and it runs on my computer with access 2010.
heres what i need help with:
One part of the edit form has a add product/vendor button. this button bring up another form linked to another table and allows the user to add the information needed. Then when u save that record that form closes and brings u back to the origanl form. On this form under the add product vendor button there are 2 listboxes next to each other. The one on the left shows the products associated with the client and the one on the right shows the vendors that client uses for that product listed on the right. heres the problem. I did this with vd code, as follows:
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL2 As String
strSQL2 = "SELECT Vendors from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List93.RowSource = strSQL2
but when i open it in a the newer version of access or if i try to make it an ACCDE, it doesnt work properly. It just shows fills the list for the first client, but when i go to the next client(record) it remains unchanged and still shows that first clients prod and vendors in the listboxes.
ANY HELP IS GREATLY APPRECIATED!

That code needs to be put into the form's current event. That way it will run every time the current record changes.

If you put it in a sub then you can reuse it (in the current event and after the button is pressed) by just calling the name of the sub rather than having the same code appear twice in two different places.

Thanks for the quick response, I really appreciate it.

As you can see below, I think it is in the current event like u said

Private Sub Form_current()
Dim strSQL As String
strSQL = "SELECT Products from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List91.RowSource = strSQL
Dim strSQL2 As String

strSQL2 = "SELECT Vendors from [Client ProdVend] " & _
"Where Client_Account_Name = '" & Me.Client_Account_Name & "'"
Me.List93.RowSource = strSQL2

End Sub

what do u think i need to change....i works 100% in access 2010, but when i make it a accde or open in different access version, it doesnt work properly

It looks fine to me. Are you sure code is allowed to run under these other circumstances? The database is in a trusted location (Trust Centre settings) etc.

Can you compile the VBA before making it into an accde?

I can only assume something is stopping code execution. To check for that, put a msgbox in the current event. Check that appears every time you move to a new record in the form.

ok, so i am makeing an assumption, and i dont know if its right or not, but i cant test it since i cant go on a computer with a different version of access at the moment. I assume the problem that its not working when i make accde is the same as y its not working whe i open it in another version, since the same thing is happening. so i put a message box in. And ti shows up on every record when i do it in access(remember the database works properly on this computer when not in accde) but when i make accde it doesnt show up on any of the records,.....this explains y its not working....but, i cant figure out whats stoping the vb code. hmmmmm

also, it was running on a trusted location, so that shouldnt have been the problem

Except different versions of Access have different trusted locations (different reg keys for them).

But still, I doubt that's it: you'd get messages telling you code has been disabled.

I don't know what to suggest. When opened in the earlier versions (as the accdb), go into the VBA editor and check the references (make a note of what they are on yours when they work). Perhaps that's what's causing no code to run - a reference it can;t find.

Thanks so much for your help, like i said this is one of my first real projects in access so I might be just missing something really obvious. It seems like the code just isnt coming up at all when i check it in the accde. I attached 2 screenshots, maybe looking at that , it will make more sense.

thanks a bunch

Yeah you won't see any VBA in the accde because it's compiled. That's normal.

Have you tried creating a new emty database and importing everything into it? There's always the chance with Access that any hard to explain problem is some sort of corruption.

ok, the new db and importing everythign idea worked.....now i can make an accde file that works....i really hope that fixed the problem with the other versions of excel...it seems weird to me that that would be the problem, but hopefully

It doesn't seem wierd to me. Access databases get bloated and corrupted. That's third in the list of certainties in life - after death and taxes. I expect it is sorted and glad to hear it.

it worked..thanks