Multiple Combo Boxes

  • Thread starter Thread starter Priss
  • Start date Start date
P

Priss

I understand that everyone cannot get a response right
away. But I have been sending emails and code and really
need some assistance. If so could please send me an
example of linking (3)cbo to a subform and generating data
based on the choice of cbo selection made, I would really
appreciate it. I have tried everything I could think
of,including posting my code about 1-2 months ago. Still
no response. Please Help!!!
 
That is exactly what I need the form to do. I have posted
my existing code, but not getting much of an answer. Not
sure if I am providing enough informatio, or it is too
complex to work with.
 
I understand that everyone cannot get a response right
away. But I have been sending emails and code and really
need some assistance. If so could please send me an
example of linking (3)cbo to a subform and generating data
based on the choice of cbo selection made, I would really
appreciate it. I have tried everything I could think
of,including posting my code about 1-2 months ago. Still
no response. Please Help!!!
Click to expand...

A clearer question would help greatly.

You don't "link" combo boxes to a subform. A combo box is a control
(like a checkbox or a textbox) which can be included *on* a subform. A
combo box also does not "generate" data; it allows the user to select
a record from the combo box's Row Source (typically a table, though it
can be a list of values) and store that selection into the Subform's
recordsource table. No code whatsoever is needed to do this.

There is an example on your Access CD showing how this is done: the
Northwind sample database. Take a look at the Orders form; there are
two combo boxes on the OrderDetails subform.

If you're asking about something else, please explain in more detail
just what you mean.

John W. Vinson[MVP]
(no longer chatting for now)
 
Once again, I have sent code because I clarified that I
was new to this and perhaps I was not explaining
clearing. The examples did not help on the Northwind, my
reason for sending my code in for further assistance.
Usually if you don't know whole story, you let someone
else that does handle it. What you said is not helping me
at all - Thank you
 
Once again, I have sent code because I clarified that I
was new to this and perhaps I was not explaining
clearing. The examples did not help on the Northwind, my
reason for sending my code in for further assistance.
Usually if you don't know whole story, you let someone
else that does handle it. What you said is not helping me
at all - Thank you
Click to expand...

I apologize for the tone of my remarks.

However, you say "I have sent code". There was no code in your
message. There are thousands and thousands of messages on this
newsgroup; I'd need to do a detailed google search to find your code,
and it would be difficult since a vast number of these posts are from
your reply address, (e-mail address removed).

Could you please repost the code in a reply to this message, keeping
the same Subject line? I'll certainly try to respond.


John W. Vinson[MVP]
(no longer chatting for now)
 
Darren
I really appreciate your assistance in trying to help
solve the problem and being understanding. The code is
out there under the same name for the month of September.
Just not sure what date. But once again, thanks for your
help!!!
 
I know this can be very frustrating at times and I do
apologize as well for thinking the worse because I had not
gotten a response from previous post of the same issue.
Here is the code that I have provided w/revisions:

Private Function FilterData() As DAO.Recordset
On Error GoTo err_routine

Dim ErrorValue As String
Dim APPSYSID As String
Dim SalesID As String
Dim FilterValue As String
Dim SQL As String
Dim rs As DAO.Recordset
Dim myDB As DAO.Database

' get the value of the Error from the combo box
Me.cboError.SetFocus
ErrorValue = Me.cboError.Text

' get the value of the AppSysID from the combo box
Me.cboAppID.SetFocus
APPSYSID = Me.cboAppID.Text

' get the value of the SalesID from the combo box
'Me.cboSalesID.SetFocus
'SalesID = Me.cboSalesID.Text

' get an instance of the database
Set myDB = CurrentDb

' set up the filter
SQL = "SELECT * FROM [tblExclude] "

If Len(Trim$(ErrorValue & "")) > 0 Then
'we have an error value

If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & ""

Else
'we do not have an app sys id
SQL = SQL & "where [Error]=" & ErrorValue

'we do not have a error value

End If

Else

If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & APPSYSID

Else
'we do not have an app sys id
'do not change the SQL
End If
End If

Set rs = myDB.OpenRecordset(SQL)
Set FilterData = rs
Exit Function

err_routine:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbOKOnly + vbInformation, "Error"
Set FilterData = Nothing
Exit Function

End Function

Private Sub cboAPPID_AfterUpdate()
Set Me.Recordset = FilterData

End Sub

Private Sub cboError_AfterUpdate()
Set Me.Recordset = FilterData

End Sub

Private Sub cmdSwitchboard_Click()
On Error GoTo Err_cmdSwitchboard_Click

Dim stDocName As String

stDocName = "mrcSwitchBoard"
DoCmd.RunMacro stDocName

Exit_cmdSwitchboard_Click:
Exit Sub

Err_cmdSwitchboard_Click:
MsgBox Err.Description
Resume Exit_cmdSwitchboard_Click

End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


DoCmd.Close

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

Private Sub cmdEmail_Click()
On Error GoTo Err_cmdEmail_Click

Dim stDocName As String

stDocName = "mcrEmail"
DoCmd.RunMacro stDocName

Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

End Sub

Private Sub Description_AfterUpdate()
Me!tblExclude_Error!Description = Me!Description

End Sub

Private Sub Form_Load()
'clear screen
cboError = ""
cboAppID = ""
'cboSalesID=""

cboError.SetFocus

End Sub
 
I know this can be very frustrating at times and I do
apologize as well for thinking the worse because I had not
gotten a response from previous post of the same issue.
Here is the code that I have provided w/revisions:
Click to expand...

Comments inline.
Private Function FilterData() As DAO.Recordset
On Error GoTo err_routine

Dim ErrorValue As String
Dim APPSYSID As String
Dim SalesID As String
Dim FilterValue As String
Dim SQL As String
Dim rs As DAO.Recordset
Dim myDB As DAO.Database

' get the value of the Error from the combo box
Me.cboError.SetFocus
ErrorValue = Me.cboError.Text
Click to expand...

You can also determine the contents of a control by examining its
Value property. It's not necessary to setfocus to it in that case. If
you Dim ErrorValue as a Variant instead of a String it will accept
NULL - see below.
' get the value of the AppSysID from the combo box
Me.cboAppID.SetFocus
APPSYSID = Me.cboAppID.Text

' get the value of the SalesID from the combo box
'Me.cboSalesID.SetFocus
'SalesID = Me.cboSalesID.Text
Click to expand...

Commented out intentionally?
' get an instance of the database
Set myDB = CurrentDb

' set up the filter
SQL = "SELECT * FROM [tblExclude] "

If Len(Trim$(ErrorValue & "")) > 0 Then
'we have an error value

If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & ""
Click to expand...

If Error is a Text datatype field you'll need the syntactically
required quote marks:

"where [Error] = '" & [ErrorValue] & "' and...

If it's numeric your code should work as written.
Else
'we do not have an app sys id
SQL = SQL & "where [Error]=" & ErrorValue

'we do not have a error value

End If

Else

If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & APPSYSID
Click to expand...

Same concern about Text fields.
Else
'we do not have an app sys id
'do not change the SQL
End If
End If

Set rs = myDB.OpenRecordset(SQL)
Set FilterData = rs
Exit Function
err_routine:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbOKOnly + vbInformation, "Error"
Set FilterData = Nothing
Exit Function

End Function

Private Sub cboAPPID_AfterUpdate()
Set Me.Recordset = FilterData

End Sub

Private Sub cboError_AfterUpdate()
Set Me.Recordset = FilterData

End Sub

... <code snipped, irrelevant to this problem>

Private Sub Description_AfterUpdate()
Me!tblExclude_Error!Description = Me!Description

End Sub

Private Sub Form_Load()
'clear screen
cboError = ""
cboAppID = ""
'cboSalesID=""
Click to expand...

Set these to NULL rather than to a zero length string.
cboError.SetFocus

End Sub
Click to expand...

So... a couple of questions:

- What's happening when you select a value from the combo box?
- What do you WANT to happen?

If you just want to display records from tblExclude which match the
combo box criteria, there are two perhaps easier ways to do so. First,
build your SQL and set the RecordSource property of the Query to that
string (since the Recordsource is a string property, not a Recordset
property); second, use *no code at all*, and instead put a Subform on
your form based on tblExclude. Set its Master Link Field property to

[cboError];[cboAppID];[cboSalesID]

and its Child Link Field to

[ErrorID];[AppID];[SalesID]

It should filter the data on the basis of these fields. You'll
probably need to Requery the subform in the AfterUpdate event of each
combo box.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
By selection of combo boxes to provide data in the
subform. If I don't select any combo, then ALL records
show in the subform. (i.e. select combo1 - shows records
equal to combo1, then I select combo2 as - shows records
equal to combo1 and combo. Same with combo30. Not sure
if I am making any sense. My combo1 works when selected,
but combo2 comes up by default for whatever APPID is
first. I made the change with the master/child link -
combo1 & combo2 works, but combo3 will not. So i know it
has something do with what I am doing. I would like not
have to code at all to make this work since I am not
experience with it. I am also trying to understand the
other code changes that you specified - working with it.
-----Original Message-----
I know this can be very frustrating at times and I do
apologize as well for thinking the worse because I had not
gotten a response from previous post of the same issue.
Here is the code that I have provided w/revisions:
Click to expand...

Comments inline.
Private Function FilterData() As DAO.Recordset
On Error GoTo err_routine

Dim ErrorValue As String
Dim APPSYSID As String
Dim SalesID As String
Dim FilterValue As String
Dim SQL As String
Dim rs As DAO.Recordset
Dim myDB As DAO.Database

' get the value of the Error from the combo box
Me.cboError.SetFocus
ErrorValue = Me.cboError.Text
Click to expand...

You can also determine the contents of a control by examining its
Value property. It's not necessary to setfocus to it in that case. If
you Dim ErrorValue as a Variant instead of a String it will accept
NULL - see below.
' get the value of the AppSysID from the combo box
Me.cboAppID.SetFocus
APPSYSID = Me.cboAppID.Text

' get the value of the SalesID from the combo box
'Me.cboSalesID.SetFocus
'SalesID = Me.cboSalesID.Text
Click to expand...

Commented out intentionally?
' get an instance of the database
Set myDB = CurrentDb

' set up the filter
SQL = "SELECT * FROM [tblExclude] "

If Len(Trim$(ErrorValue & "")) > 0 Then
'we have an error value

If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & ""
Click to expand...

If Error is a Text datatype field you'll need the syntactically
required quote marks:

"where [Error] = '" & [ErrorValue] & "' and...

If it's numeric your code should work as written.
Else
'we do not have an app sys id
SQL = SQL & "where [Error]=" & ErrorValue

'we do not have a error value

End If

Else

If Len(Trim$(APPSYSID & "")) > 0 Then
'we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & APPSYSID
Click to expand...

Same concern about Text fields.
Else
'we do not have an app sys id
'do not change the SQL
End If
End If

Set rs = myDB.OpenRecordset(SQL)
Set FilterData = rs
Exit Function
err_routine:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbOKOnly + vbInformation, "Error"
Set FilterData = Nothing
Exit Function

End Function

Private Sub cboAPPID_AfterUpdate()
Set Me.Recordset = FilterData

End Sub

Private Sub cboError_AfterUpdate()
Set Me.Recordset = FilterData

End Sub

... <code snipped, irrelevant to this problem>

Private Sub Description_AfterUpdate()
Me!tblExclude_Error!Description = Me!Description

End Sub

Private Sub Form_Load()
'clear screen
cboError = ""
cboAppID = ""
'cboSalesID=""
Click to expand...

Set these to NULL rather than to a zero length string.
cboError.SetFocus

End Sub
Click to expand...

So... a couple of questions:

- What's happening when you select a value from the combo box?
- What do you WANT to happen?

If you just want to display records from tblExclude which match the
combo box criteria, there are two perhaps easier ways to do so. First,
build your SQL and set the RecordSource property of the Query to that
string (since the Recordsource is a string property, not a Recordset
property); second, use *no code at all*, and instead put a Subform on
your form based on tblExclude. Set its Master Link Field property to

[cboError];[cboAppID];[cboSalesID]

and its Child Link Field to

[ErrorID];[AppID];[SalesID]

It should filter the data on the basis of these fields. You'll
probably need to Requery the subform in the AfterUpdate event of each
combo box.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.
Click to expand...
 
Back
Top