Multiple queries in an event

[Nancy]

Hi -

I am so close getting this form complete and really am stuck on the final
part (read through everything my questions are at the end). I have a form:

Heading:
• ASSY1 - list box field. Unbound and gets its data from a query which gets
me the distinct ASSY1 fields. I
• VDESC – from the im_table where the PART_NO = ASSY1. Currently actual
field, but could be unbound.
• UM – from the im_table where PART_NO = ASSY1. Currently actual field, but
could be unbound.

Detail
• Fields: ASSY, PART, VDESC, UM, BOM_ID, REF, QPA, PROJECT, COMMENTS, RELEASE
• They come from the ps_table and the im_table.
• Actual fields not unbound.

On the ASSY1 field, I have placed the following a code on the AFTER_UPDATE
event. I had wanted to attach it to a command button, but could not get one
to work.

if Len(assy1) > 0 Then
cSQL = "SELECT PSF_TABLE.ASSY,
PSF_TABLE.PART,IM_TABLE.VDESC,IM_TABLE.UM,"
cSQL = cSQL & "PSF_TABLE.BOM_ID, PSF_TABLE.REF, PSF_TABLE.QPA,
PSF_TABLE.PROJECT,"
cSQL = cSQL & "PSF_TABLE.PROJECT_EFF, PSF_TABLE.COMMENTS,
PSF_TABLE.RELEASED "
cSQL = cSQL & "FROM PSF_TABLE, IM_TABLE "
cSQL = cSQL & "WHERE PSF_TABLE.ASSY = '" & Me!assy1 & "'"
cSQL = cSQL & " AND PSF_TABLE.PART = IM_TABLE.PART "
cSQL = cSQL & "ORDER BY PSF_TABLE.PART"
End If

Me.AllowAdditions = False

Me.RecordSource = cSQL

Okay, this works like a champ for populating the detail record. However, I
have no idea for populating the VDESC and UM fields in the heading. There
will always be a 1 to 1 relation ship where im_table.PART_NO = ASSY1 and
multiple rows in the detail.

Is there a way I can get all of the fields in the detail and heading to get
populated?
Can I have multiple select statements in an event? If so, how do I execute
them separately?

Any help/direction you can offer is appreciated.

 

[Carl Rapson]

You could use DLookUp to populate the two field values. Make them unbound
controls, and put the DLookup in your AfterUpdate event right after you set
the form's RecordSource.

Carl Rapson