Combo Box blank & Combo Boxes not working on Subform

[adyas]

I realise that my queries may be very basic but I know nothing about SQL and have spent a whole day struggling to get these 2 problems resolved. I'd be very grateful for any help. I've attached a copy of the database.

Problem 1 - Combo Box Blank
With the use of Access Help I have managed to get the first 2 combo boxes of 3 to link perfectly. However I can't get the third box working.

The Service Type field within FRMJobRequests, when clicked on, should display the Service Type and its Project Code. Then upon selection only the Project Code should appear in the combo box. I'm just getting a blank.

The query I'm using is: QRYCBOProjectCode, which looks like:

SELECT TBL3BuildingServiceType.projectcode, TBL3BuildingServiceType.servicetype
FROM tbl2sitebuildings INNER JOIN tbl3buildingservicetype ON tbl2sitebuildings.BuildingName=tbl3buildingservicetype.BuildingName
WHERE (((tbl2sitebuildings.Buildingname)=Forms!FRMJobRequests.cbobuilding));


Problem 2 - Combo boxes don't work on a subform
Again, with the use of Access Help I've managed to get the combo boxes in FRMSSupplierInvoicePayments to work perfectly. However when I set this form as a subform in FRMJobRequests it all goes pear shaped. I realise I need to include the FRMJobRequests somewhere in the query but I'm at a loss as to how to do this.

The query I'm using is QRYCBOSitesBuildings, which looks like:

SELECT TBL2SiteBuildings.Buildingname
FROM tbl1sites INNER JOIN tbl2sitebuildings ON tbl1sites.siteid=tbl2sitebuildings.siteID
WHERE (((tbl1sites.siteID)=Forms!FRMJobRequests.cboSite));

 

[boblarson]

Well the problem with your combo for service types is that you have included the wrong tables. You should have a table with the service types that can be selected (basically a lookup table) and then you use THAT for your rowsource. The tbl3buildingServiceType table is just where you STORE the selection, not where you get the service types from.

 

[adyas]

Thank you for your help Bob, I'm very grateful. I'm working on this as we speak.

Do you have any advice for the second problem of the combo boxes not working in a subform? I've searched through the forum and found a one identical problem but it has never received a reply so I'm still stuck on that one. If not I'll figure it out and in the meantime I've set up a button to open the form.

Thanks again for your time.

Allison

 

[boblarson]

Do you have any advice for the second problem of the combo boxes not working in a subform?

I will need a little more information, like what actually happens. What do you mean by "it goes pear shaped?" What is the exact behavior?

 

[adyas]

Sorry Bob that wasn't very clear was it.

I have set up 3 combo boxes in the form FRMSSupplierInvoicePayments and they work perfectly.

However when I insert FRMSSupplierInvoicePayments into the form FRMJobRequests, as a subform, they stop working. What I get is a dialogue box:

Enter Parameter Value
Forms!FRMSSupplierInvoicePayments.cboTaskID

I understand that I probably need to edit the queries:
QRYCBOMatrixCostElement
QRYCBOMatrixServiceCode

in order that they relate to FRMJobRequests. However I don't know enough about SQL to get the form or wording correct.

By the way I got the other problem sorted and its working well. I've attached a revised copy of the database so that you can see the problem.

Regards

Allison

 

[boblarson]

The problem with the form reference in the queries is that when put on a subform you have to use the syntax to get to the form through the main form. So, what I usually suggest is this method to get it:

http://www.btabdevelopment.com/ts/refer2sfrms

 

[adyas]

Hi Bob

I did have a look at the information in the link and that will be very useful for something else I'm working on. However what I did find on the site that fits perfectly is this sentence from another related topic called:

Easy way to remember how to refer to subforms
Now, if you need to refer to another form, you can use this, what I'll call somewhat confusing, way of doing so:

Forms!YourMainFormName.YourSubformContainerName.Form.YourControlName

I knew it would be something simple and this is it. What I'd missed out were the bits in bold.

Great website by the way, very clear instructions.

These two problems have been a real headache for a couple of weeks now so thank you. I'm very grateful for your direction.

Allison