Thanks Jack. I know this would work great, if I can only remember what I am
doing here. I haven't used access in about 7 months now and it seems I have
forgotten everything I know about it. This use to be a simple thing for me
but now I can figure it out anymore. I need some serious help here.
I have 2 Tables: tblSiteList and tblMaterialsDatabase
tblSiteList have Site Id, NLP and Market where:
Site Id has 8 characters in its fields i.e 1AT1059D and the
NLP field are NLP1, NLP2, NLP3 and NLP1 Infill
tblMaterialsDatabase have Site ID, NLP and Market where:
Site ID had 7 charecters in its field i.e 1AT1059 and the
NLP field are 1, 2, 3 and 1 Infill
I created a form, frmSiteList, with tblSiteList as the Record Source and on
that form I want to put a TabCtl with a few Pages. One of those pages in
named "Materials." And my intension is to place a ComboBox on this Page whose
Control Source would be a field in tblMaterialsDatabase called "Shopping
Cart."
So based on the values in Site Id, NLP and Market of frmSiteList I want the
list displayed in the combobox to be a subset of the entire list in the
Shopping Cart field. This is because I want to also have a Subform in the Tab
Page to display the result of a query based on the value in the combobox and
the values in Site Id, NLP and Market of frmSiteList.
This is what I am trying to do in a nutshell. But I can't figure out how to
make the combobox display the subset of data based on the criteria that I
listed above. That is what I need help with if I can find it.
I know I can get back into this if I can just get some help to get me
started in the right direction. I have done a lot of work in access before
but now my brain is kinda oozzie right now and I can't figure out what it is
that I am not seeing. Any help will be greatly appriciated.
Ayo
Jack Leach said:
Usually if I want a control to be based of a number of other controls, I'll
put some code behind each of the 'monitored' controls to update the combo.
With three controls, you can use a function to build the sql string. Like so:
Private Sub SiteID_AfterUpdate()
Call psBuildComboSQL
End Sub
Private Sub Market_AfterUpdate()
Call psBuildComboSQL
End Sub
Private Sub NLP_AfterUpdate()
Call psBuildComboSQL
End Sub
Private Sub psBuildComboSQL()
Dim strSQL As String
strSQL = "SELECT [Shopping Cart] " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([SiteID] = """ & Me.Site_ID & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NPL] = """ & Me.NPL & """) " & _
"ORDER BY [Shopping Cart]"
Me.cmbobox.RowSource = strSQL
Me.cmbobox.Requery
End Sub
Question: Why are you using a Left() and Mid() function to get portions of
a value? You should never have to break down a value to further retreive any
information from it.... if it contains more than one peice of data it needs
to be in it's own field. Occasionally you will contencate a few values
together, but the only reason for this is for a display to the User and
should not be used for anything other than that.
hth
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
Ayo said:
I haven't used Acces in a while and I am getting kinda rusty. Also the 2007
interface is kinda disorienting. This is what I need help with; I have a form
with text boxes also I have a TabCtl control with 2 pages. On one of the
pages I have a Combobox control with the following click event
Private Sub Page26_Click()
Me.cmbShoppingCart.RowSource = "SELECT DISTINCT [Shopping Cart] FROM
tblMaterialsDatabase where " & _
"(tblMaterialsDatabase.[Site ID] ='" & Left(Me.Form.Site_Id.Value,
7) & "') And (tblMaterialsDatabase.Market='" & Me.Form.Market.Value & _
"') And (tblMaterialsDatabase.NLP ='" & Mid(Me.Form.NLP.Value, 4) &
"')" & " ORDER BY [Shopping Cart]"
End Sub
The problem is I can't get it to work. I need the comboBox to update based
on the values in the form's Site ID, NLP and Market txtboxes
