Related Combo boxes

[zapzip]

Hi all-

I have been struggling combo boxes for days and would appreciate any ideas. The first combo box lists sites and the second Leases. What I am after is after selecting a site that only the leases for that site are dispalyed in the second cbo.

Form Record Source is M_ReceiptSlip

Site is defined by cboSites_IDs whose control source is ReceiptSlipSites_IDs
Row Source a query

 SELECT L_Site.Site_ID,
 L_Site.SiteAbbrev, 
L_Site.SiteName 
FROM L_Site ORDER BY [SiteName];

Bound Col 1
Col widths 0;0;1

Lease Name is defined by cboLeases_IDs whose control source is
ReceiptSlipLeases_IDs
Row Source a query

SELECT L_Lease.LeaseName, 
L_Lease.LeaseSites_IDs, 
L_Site.Site_ID, 
L_Site.SiteName
FROM L_Site 
INNER JOIN L_Lease 
ON L_Site.Site_ID = L_Lease.LeaseSites_IDs
WHERE (((L_Site.SiteName)=[Forms]![F_ReceiptSlip]![cboSites_IDs]))
ORDER BY L_Lease.LeaseName;

The table L_Site has primary key Site_ID related to table L_Lease foreign key LeaseSites_IDs.

The 1st query works fine but the second returns no records.
I have tried moving the Where [Forms]![F_ReceiptSlip]![cboSites_IDs] but still get blanks. I think this reference is the problem since when I enter an actual value as the criteria records are correctly returned. I can't seem to contect it to the value in the form though.

Any suggestions greatly appreciated. Thanks

 

[Spenney]

Is it that your WHERE clause is trying to evaluate a text value (SiteName) with a numeric value (cboSites_IDs)

Try swapping L_Site.SiteName for L_Site.SiteID

Also, see this thread which probably covers what you need...

http://www.tek-tips.com/viewthread.cfm?qid=743011

 

[zapzip]

Thanks for the links... I have been working on this all day and got it running as long as the second combo box is not bound to a table. The table expects a numeric value and the code sql is returning a text value- hence an error.

Any suggestions on how to bind the values in the 2 combo boxes to a table?

Thanks again for your suggestions and pointing me in the right direction.

 

[Spenney]

What is the bound column of the Lease combo? I guess it should be column 2 from your code above, though I would alter the order so that SiteID is column 1 with a width of 0. If I understand correctly the row source of the second combo should be something like...

SELECT L_Lease.LeaseSites_IDs, L_Lease.LeaseName, L_Site.Site_ID, L_Site.SiteName
FROM L_Site 
INNER JOIN L_Lease 
ON L_Site.Site_ID = L_Lease.LeaseSites_IDs
WHERE (((L_Site.SiteID)=[Forms]![F_ReceiptSlip]![cboSites_IDs]))
ORDER BY L_Lease.LeaseName

Bound column = 1
Column widths = 0;3;0;0

Doing this would mean the value of 'cboLeaseID' (or whatever it's called) would be numeric and would be valid for the table you're tyring to update. Any good?

 

[zapzip]

Spenney- Thanks for your code & reply.

I have used an AfterUpdate Event to generate the ControlSource of the second combo box. It all works. However, (allways seems to be more) I can't get the combo box records into my M_ReceiptSlip table. If I set the 2nd control box control source to ReceiptSlipLeases_IDs, there is a conflict. The code AfterUpdate returns a text value whereas the table expects a numeric foreign key link.

I have changed some stuff to get it all to this point and also previously left out some info (which I didn't know was needed) so I listed it below. Disregard the previous posting tables, and code.



two combo boxes

                    1st                    2nd
Name:           cboSites_IDs          cboLeases_IDs
ControlSource:  ReceiptSlipSites_IDs  [COLOR=red]ReceiptSlipLeases_IDs [/color]
RowSource:      L_Site                blank
ColumnCount:      3                      1
ColumnWidths:   0;0;1                    1
BoundColumn:      1                      1 
Event           AfterUpdate           none

[COLOR=red] this generates errors [/color]

AfterUpdate Event

Private Sub cboSites_IDs_AfterUpdate()
Me.cboLeases_IDs.RowSource = "SELECT LeaseName FROM" & _
   " L_Lease WHERE LeaseSites_IDs = " & Me.cboSites_IDs & _
   " ORDER BY LeaseName"
Me.cboLeases_IDs = Me.cboLeases_IDs.ItemData[COLOR=red](5)[/color]
End Sub

If you “decode” above you get the follow SQL statement which could paste into Query design window & test it out (at least to some extent)

SELECT LeaseName FROM L_Lease WHERE LeaseSites_IDs = Me.cboSites_IDs ORDER BY LeaseName;


3 Tables

L_Site
pk	Site_ID
	Site Abbrev
	SiteName
	Etc

L_Lease
pk	Lease_ID
	LeaseName
	LeaseActive
	LeaseDisplayOrder
	LeaseAbbrev
fk	LeaseSites_IDs		linked to L_Site Site_ID
fk	LeaseSiteSub_IDs

M_ReceiptSlip
pk	ReceiptSlip_ID
	ReceiptSlipNumber
	ReceiptSlipDate
	ReceiptSlipDollarCollect
	ReceiptSlipActualPayee
	ReceiptSLipGoPrint
	ReceiptSLipPrintFlag
	ReceiptSlipPrintComment
fk	ReceiptSlipSites_IDs	linked to L_Site Site_ID
fk	ReceiptSlipSiteSubs_IDs	linked to another table
fk	ReceiptSlipLeases_IDs	linked to L_Lease Lease_ID

After being so long winded, the current problem boils down to getting the combo box records into a table. Your ideas and comments would be greatly appreciated (and needed).
Thanks