[gcrutch]

I have a form it has several text boxes, fields and a subform. It has a parameter that prompts for a SerialNbr.
The fields on the form are SerialNbr, cboCartStatus, CartDate, CartID, ServiceID
Subform fields: cboCartEvent, CartID, CartEventSubType, CartEventDate

The control source for the form is a query that uses the table tbl_Cart. The fields in the tbl_Cart are: CartID, SerialNbr, ServiceID, CartStatus, CartStatusDate
And the tbl_Cart is related to the tbl_Services and its fields are: ServiceID, AddressID, ServiceStatus, NbrCarts, etc

The AddressID comes from a view which is the basis for the entire application. So a veiw was created based on the Services table and the vAddress view this is where the vService_Address comes from. It returns all matching records that has an address that's related to a service.

Hence, what the form does is... when the form is opened it asks the user for a SerialNbr. ONce the serial nbr is entered it brings up the record that is associated with that SerialNbr.


The following pseudocode was given to me saying this is how I need to do it. Can someone help me turn this pseudocode in to VBA code.

I have attached a copy of my tables, query and form.

On the Cart Form, insert an Address label under the “Cart Update” title.
In the Form Load subroutine, set the default caption to “Address Unknown”.
Query the Cart table using the Serial Number as the parameter.
If the query returns a record, check the ServiceID.
If it is not null, then query view vService_Address using the ServiceID as the parameter.
Set the Address label to the concatenated address returned by the query

[ScottGem]

There is an easier way to do this. Add a header to the form. Use the combobox wizard to create a Search combo. You can then enter a serial number to bring up the serial number to retrieve that record.

[gcrutch]

I can retrieve the record fine when I enter the serialnbr. In addition to that, I want to be able to see the address as well.

[gcrutch]

I basically just need to know how to write the code to
Do this:
Query the Cart table using the Serial Number as the parameter.
If the query returns a record, check the ServiceID.
If it is not null, then query view vService_Address using the ServiceID as the parameter.
Set the Address label to the concatenated address returned by the query

[ScottGem]

Sorry but I'm not clear here. The purpose of subforms is to show related data. When you enter the serial number does the data you want show up on the form someplace? If so, where.

If all you want to do is print an address label for the associated address, then you need one line of code:

DoCmd.OpenReport "labelname",, "[AddressID] = " & Me.AddressID

[gcrutch]

OK if you were to query a table called Carts using the field called SerialNbr as your parameter how would you write the statement to query that table?

[gcrutch]

Everything works fine... I just need to add one more piece of information to the form and that's the Address.

[ScottGem]

Then add a subform to your form, linked on the appropriate field and bound to the address table. If the address field is already a part of the recordsource of either the main form or a subform, then add it as a control.

A query returns a result set, not a single value. If you want to return a single column from a query for a single record use a DLookup.

[gcrutch]

Yes I am returning a result set I guess... the address consist of address, street, suffix. That's why I wanted to concatenate the address returned by the query.

[ScottGem]

If you want to concatenate the address parts, do it in the query, then use the one Query column