Need Help with SQL Statement

[Hank44]

I have 2 forms. Both have a field (MasterBillNo) I already have an on click event that navigates from the 1st form to the second. I need to search the <Airwaybills> table for all of the records that have the same (Masterbillno) and automatically populate some fields on the next form based on the 1st record returned in the recordset. The fields are (AirlineName),(Flightdate)



The 1st form has the (Masterbillno) field to use in a search string. There will be several records that have the same (MasterbillNo)

I need this to work with my navigational on_click event.

Can anyone help?

[ssanfu]

Hi Hank,

You gave very little to go on. I've read this several times and I still don't have any idea on how to help - could you be a little more specific? (remember - we can't see your mdb)

Where is the SQL? What is the table structure, how are they related, what code do you have? Maybe attach a copy of your mdb?

[NoellaG]

Hi Hank,

it's been a few years since I worked with airwaybills, so correct me if I'm wrong, but as I remember it the bill number is a text field. Then the sql string shoulb be build like:

strSQL = "select * from Airwaybills where Masterbillno = """ & me.Masterbillno & """"

if the bill nr. is a number field you don't have to include the strings so it should read as

strSQL = "select * from Airwaybills where Masterbillno = " & me.Masterbillno

greetings
NG

[SkillForge]

If I'm understanding your question correctly, the simplest way to do this is to create a query that the new form will be based on (to use as the Record Source of the second form.)

In the query you can reference a field value on any other active form in Access. So in the QBE grid, in the criteria row (for the field that you want to search the airbill numbers) and use the value from the field on the first form as the criteria value. The syntax would be:

=[Forms]![FormName]![FieldName]


-J