Problem with Filtering a Look Up (in subform) using parameters on main form.

[tim_tims33]

Hi,

I am creating a booking database for a Bus Hire company. There is also an Invoice element to it, which will Invoice customers for a range of bookings that they've made.



You will see in the screen image, the relationships between the four tables:
-tblInvoice
-tblInvoiceDetails
-tblCustomer
-tblBooking

IN tblBooking (see screen dump) the customer is allocated a booking number and so on.

However, at the end of the month, the Customer will be invoiced for specific bookings. I would like to, firstly, select the customer in the Invoice form, then in the InvoiceDetails subform, select THE BOOKINGS THEY HAVE PREIVOUSLY Made using a look up. (see screen dump called frmInvoice.Gif) Obviously, i don't want every single booking to appear!

I have applied a criteria to the data source for the look up. (see image datasource...GIF) with
forms![frmInvoice]![CustomerID] in the hope that the query will search for BookingIDs where the currently selected CustomerID (in Form Invoice) finds a corresponding match in tblBooking.

This isn't working!! Why not??

If anyone has any ideas, that'd be much appreciated.

Thanks

tim

[June7]

This is a form/subform? Master/Child links properties are set? The query you show is for the BookingID combobox? Do you want to provide project for analysis?

[tim_tims33]

This is a form/subform? Master/Child links properties are set? The query you show is for the BookingID combobox? Do you want to provide project for analysis?

I have attached the DB.

Yes, it is a Form with subform InvoiceDetails.

If you can shed any light on the matter,it'd be appreciated

thanks

[June7]

I added some test data. The BookingID combobox shows only the bookings associated with that customer. What you need is code that will requery the combobox after selection of customerID for a new invoice record. Use the AfterUpdate event of CustomerID combobox. I use only VBA but macro can probably do it.

I always give subform container control a name different from the object it holds, like ctrDetails and name controls different from the field they are bound to, like cbxBookingID. So in VBA:
Forms!frmInvoice.ctrDetails.Form.cbxBookingID.Requ ery

[tim_tims33]

Hi, thanks for you reply...but this doesn't deal with the problem.

You said that the BookingID combo box shows only the bookngs associated with that customer. That's what I am trying to achieve. If this has worked for you...then that seems fairly strange.

For example, having already selected customerID 2, let's say, in the main form...
...in the corresponding subform, i would like to click on combo BookingID and see the bookings appear JUST FOR THAT CUSTOMER.

Currently, I am seeing all bookings every made.

From what i understand in your solution... you are suggesting I requery the subform's data everytime i change the customerID. But, I only select the CustomerID once at the top and select their related bookings in the subform correspondingly.

Hope this makes sense.

thanks

[June7]

I should have said that the BookingID combobox shows only the bookings for the customer when the form first opens. I am suggesting that after selecting a customer the combobox RowSource needs to be requeried, not the subform.

[tim_tims33]

I should have said that the BookingID combobox shows only the bookings for the customer when the form first opens. I am suggesting that after selecting a customer the combobox RowSource needs to be requeried, not the subform.

Hi, thanks again for your reply...but having difficulty implementing the 'requery'.

On the 'After Update' event for the CustomerID control on frmInvoice...i tried running a macro which states:
- Requery
- Control: forms!fsubInvoicedetails!cboBookingID

However, whenever i change the CustomerID on the main form... i get the following error message:
''There is no field named forms!fsubInvoicedetails!cboBookingID in the current record''.

I've probably done it wrong! Any ideas?

[June7]

I don't use macros, only VBA.

Did you give the subform container a name different from the object it holds?

Refer to my ealier post (#4) for the suggested syntax.

[tim_tims33]

I don't use macros, only VBA.

Did you give the subform container a name different from the object it holds?

Refer to my ealier post (#4) for the suggested syntax.

By container, do you mean the subform in which the control is found?

In that case, it is a form called fsubInvoiceDetails.

Following this, i tried inserting the following code in the 'after update' event of the customerID look up in the main form entitled 'frmInvoice'.

Forms!frmInvoice.fsubInvoiceDetails.cbxBookingID.R equery

Unfortunately, an error message flashes up when trying to compilte:

"...Application defined error."

Any ideas?

[June7]

I mean the subform container control in which the form is found. What is name of the subform container control what is name of form held by the control? Look at the Name and SourceObject properties of the container control.

You created a VBA procedure? You selected [Event Procedure] in the AfterUpdate event property? Click the ellipses (...) and that will take you to the procedure in VBA editor.

[tim_tims33]

I mean the subform container control in which the form is found. What is name of the subform container control what is name of form held by the control? Look at the Name and SourceObject properties of the container control.

You created a VBA procedure? You selected [Event Procedure] in the AfterUpdate event property? Click the ellipses (...) and that will take you to the procedure in VBA editor.

I'm still unsure what you mean by 'container'.

There are two forms:
form INVOICE
and
form fsubINVOICEdetails.

The BookingID lookup is found in form fsubINVOICEdetails. So, is form fsubINVOICEdetails the container?

Furthermore, if so, does the following VBA provide the solution?

Forms!frmInvoice.fsubInvoiceDetails.cboBookingID.R equery


where:
frmInvoice is teh main form...
fsubInvoiceDetails...is the subform...which contains the BookingID lookup
cboBookingID is the name of the lookup.

(I know how to apply a VBA procedure in the 'after update' event property)

Sorry for my incompetence!

[June7]

Subforms are created by putting a subform containter control on a form. The container control has a SourceObject property. The source object can be a table, query, form, report. The container control 'holds' an object. I always name the container different from the object it holds. Click once on the 'subform', this selects the container and you will see its properties in the Properties dialog, click again and you have selected the object (or some part of) within the container and see its properties.

If that clarifies, look at post 4 again for the suggested syntax.

[tim_tims33]

No...i've tried the following statement and it still doesn't work.

Forms!frmInvoice.fsubInvoiceDetails.cboBookingID.r equery

...anyone have any other ideas? ...or have I got my syntax wrong?

thanks

tim

[June7]

What is the name of the subform container control?

Forms!frmInvoice.put name of subform container control here.Form.cbxBookingID.Requery

[tim_tims33]

What is the name of the subform container control?

Forms!frmInvoice.put name of subform container control here.Form.cbxBookingID.Requery

Hi,

That's it solved! Thanks very much... I tried a different computer and put in the same criteria as I previously posted ...and some reason it worked. My container was fsubInvoiceDetails.... but i am aware that this could have a different data source.

Many thanks for your patience and help!

tim