Form and Query Question (1 Viewer)

Hi,

I am having a problem referencing a field in order to filter my listbox.
I'm been driving myself crazy with possible solutions and nothing works yet.

Please assist if you can and i will do my best to be articulate in what i need and currenty have....


TO start,
This is a form with a tab control (which should be irrelevant to my knowledge as far as syntax).

The main form uses recordsource tblClients which has a field Acct.
this Acct field is what i need to reference one way or another at a later point.

tblClients is recordsource for frmBillToShipToNew

On frmBillToShipToNew there is a subform named frmSamples which source is tblSamples also which has a field Acct to join them and id be fine if i could reference this Acct field as well but have not been able to see either.

Where i am not able to see or reference them is in a list box i created on my frmSamples form.

The listbox source is tblSamples as well just like the subform.

The listbox populates with all samples ever created however i want to filter the list to which ever account (Acct) record i am currently on.

no matter what i try whether it be vba code or inserting criteria into the Acct field in my listbox to match up with with the Acct from tblSample or frmSample or tblClients or frmBillToShipToNew, it doesnt work, however if i get prompted for input due to bad paramater query syntax and manually add an account number that i am familiar with, the listbox filters perfectly fine.

I just cant get the syntax correct.

If anyone understands my predicament and can offer any input i would be much obliged.

Thanks

BJF

Why don't you post the failing syntax and we'll fix it? Include the data type of the acct field.

Thanks for the response Pbaldy,
I unfortunately wrote the post just before walking out of the office this afternoon and will return on Monday and will post the syntax in the morning on Monday!
Thanks again and look forward to your assistance.
Sincerely, BJF

Okay. I expect something like:

strSQL = "SELECT...FROM...WHERE acct = " & Me.txtAcct
Me.Listbox.RowSource = strSQL

here is my row source for my listbox - at this point it contains all records instead of a filtered set pertaining to the parent record (Acct) that i am on.

The listbox also has a macro that filters the subform of the main form and works fine but id like to narrow the contents of the listbox down to the current Acct only.

SELECT tblSamples.SampleID, tblSamples.Acct, tblClients.Company, tblSamples.DateShipped FROM tblClients INNER JOIN tblSamples ON tblClients.Acct = tblSamples.Acct ORDER BY tblClients.Company, tblSamples.DateShipped DESC;

my macro on the after update event of the listbox is:

="[SampleID] = " & Str(Nz([Screen].[ActiveControl],0))


Maybe you can see where there lies a fault in this Pbaldy???

Thanks,
BJF

PS. with what i just pasted in here though, the listbox does in fact work to filter the subform to the correct record, just that it contains records for all customers alphabetically instead of just records for the customer record i am on in the main form.

I would expect code similar to what I posted above in the current event of the form, which fires on load and when changing records. That would change the listbox row source to filter it for whatever account is listed on the main form. It sounded like you had attempted that?