Using data from one form to search in another.

[james_liv]

Afternoon,

Im a newbie so hoping someone can help.

Using either a Macro or VB Script I need to take an invoice number from one form, switch to another form and find that invoice number in the second form.

Im really struggling and it seems a basic request.

Could someone help me.

Many thanks

 

[theDBguy]

Hi. You should be able to use the OpenForm method and the WhereCondition argument to open the second form filtered to the specific record. For example:

DoCmd.OpenForm "OtherFormName", , , "[InvoiceNumber]='" & Me.InvoiceNumber & "'"

 

[james_liv]

Hi. You should be able to use the OpenForm method and the WhereCondition argument to open the second form filtered to the specific record. For example:

DoCmd.OpenForm "OtherFormName", , , "[InvoiceNumber]='" & Me.InvoiceNumber & "'"

Thank you,

It switches form and puts a filter on it but doesn't actually find the corresponding record.

DoCmd.OpenForm "frmRepairInv", acNormal, "", "[Forms]![frmRepairInv]![Invoice Number]=[Forms]![frmRepairBooking]![Invoice Number]", acEdit, acNormal

Any ideas where I am going wrong?

Regards

 

[theDBguy]

Thank you,

It switches form and puts a filter on it but doesn't actually find the corresponding record.

DoCmd.OpenForm "frmRepairInv", acNormal, "", "[Forms]![frmRepairInv]![Invoice Number]=[Forms]![frmRepairBooking]![Invoice Number]", acEdit, acNormal

Any ideas where I am going wrong?

Regards

Hi. Please review the sample syntax I gave you. The left side of the filter should be the name of the field in your table, and the right side is the value in that field that you're trying to find/match.

 

[james_liv]

Hi. Please review the sample syntax I gave you. The left side of the filter should be the name of the field in your table, and the right side is the value in that field that you're trying to find/match.

Im sorry, I did'nt really understand and so I took what you had said, put it into a macro which I did understand and then converted it back to VBA.

DoCmd.OpenForm "frmRepairInv", , , "[Invoice Number]='" & Me.[Invoice Number] & "'"

This is how I have it now but I get a 'data type mismatch in criteria expression error.

Many thanks

 

[theDBguy]

Im sorry, I did'nt really understand and so I took what you had said, put it into a macro which I did understand and then converted it back to VBA.

DoCmd.OpenForm "frmRepairInv", , , "[Invoice Number]='" & Me.[Invoice Number] & "'"

This is how I have it now but I get a 'data type mismatch in criteria expression error.

Many thanks

If Invoice Number is numeric data type, then remove the single quote delimiters, so try it simply like this:

..."[Invoice Number]=" & Me.[Invoice Number]

 

[Dreamweaver]

This template uses a filter search form

Database Dreams

databasedreams.createaforum.com

 

[james_liv]

If Invoice Number is numeric data type, then remove the single quote delimiters, so try it simply like this:

..."[Invoice Number]=" & Me.[Invoice Number]

Hey that worked!!!!

Ive spent a day trying to work out how to do that!!!
Thank you so much! So easy when you know how.

Regards
James

 

[theDBguy]

Hey that worked!!!!

Ive spent a day trying to work out how to do that!!!
Thank you so much! So easy when you know how.

Regards
James

Hi James. You're welcome. Good luck with your project.