Displaying selected records of a Form/Subform

[Peter Bellamy]

I have a Form/Subform design which shows Returns (Form) and Goods Returned (subform) information.
They are based on 2 tables, 'Return records' and 'Goods'.
When completed it is possible on the subform, if the information has been
provided, to associate the goods with their original assembly and the unique serial number of that assembly is written into the Goods record, [aserial]

When this information is completed then a button becomes visible and onclick it will show another form which is the SerialNo Record for the assembly.

What I want to do is the reverse.
When the SerialNo Record form is opened (on its own) a button becomes visible if there are 'Return records' for that serial no.
OnClick of that button it has to open up all the Return records that have that serial number.

I have all of the above working except getting all the Return records to appear. Writing the Where condition for opening form has me beat!

This what I have tried and it seems the "Forms!.... " is unrecognised as it brings up a pop up.
"Forms![Return record]![Goods Data].Form.[goods_aserialno] = '" & Me![certgas_serialno] & "'"

Can someone guide me please?

Cheers

 

[Galaxiom]

The Where condition of an OpenForm command refers to a field in the form's record source, not a control on the form.

 

[Peter Bellamy]

So can it refer to a field in the subform's source, as I need it too?

 

[Galaxiom]

The where clause is used to limit records based on a field in its record source being compared to some value. The value can be anything from anywhere but one side has to be a field in the form's record source.

There is also the OpenArgs argument which is used to pass values with the command. These could then be used to set the subform's record source once the main form opens.

http://msdn.microsoft.com/en-us/library/aa213973(office.11).aspx

 

[vbaInet]

Would the Filter property of the subform suffice?

 

[Peter Bellamy]

The subform Table also includes the Forms Index no. I have used that and it opens up the first instance of a match. When there there is more than one match I want itto open the others as well.
The only way I can think of doing this is to retrieve all the Index nos that relate and put them in a small array (mabe less than 6) and then step through them with repeated OpenForm commands.

It seems a bit clumsy though!

 

[vbaInet]

Actually, I don't get the full picture. Your WHERE clause should just be:

"[goods_aserialno] = '" & Me![certgas_serialno] & "'"

If your serialno is a number then you would need to remove the single quote (from both ends). To ensure that you're sending the right value, save the Me!... bit into a variable and pass that instead. So:

"[goods_aserialno] = '" & mySerialNoVariable & "'"

 

[Peter Bellamy]

Vba my last last post crossed with your first.
I believe the FilterName in OpenForm has to refer to a Query, I need it to be able to open different records according to the current SerialNo record displayed.

Vba your second post.
I think as GalaxH' was saying that because [goods_aserial] is not in the Forms underlying source it won't work in the Where clause.

Cheers

 

[vbaInet]

Vba my last last post crossed with your first.
I believe the FilterName in OpenForm has to refer to a Query, I need it to be able to open different records according to the current SerialNo record displayed.

The Filter property refers to the underlying Record Source, table or query.

Vba your second post.
I think as GalaxH' was saying that because [goods_aserial] is not in the Forms underlying source it won't work in the Where clause.

Ah, alright. I thought it was referring to a field in the form's Record Source.

 

[Peter Bellamy]

I changed the source of the Form/Subform to a query that included both tables and now all is well!
Thanks for your help.

 

[vbaInet]

You're welcome. Glad you sourced it out.