Cascading combo box issue...

[negativeB]

I know that the cascading combo box issue has been addressed quite a
bit on the various message boards and groups, but I'm stuck. I have
successfully set up a series of two cascading boxes. The filtered box
returns a first name and a last name, both from two different columns
of the same table.

However, this is where my problems start.

What I would like to do is turn the first and last name into just
NAME. (i.e. Me.lblTitle.Caption = "" & [FirstName] & " " & [LastName]
& "") Is there any way to achieve this within my SQL statement in the
VB code? Or is it a control issue?

And secondly... When the filtered combo box returns a null value or an
empty recordset, I get a 2105 runtime error when I try to go to a new
record. Here's the code.

The Combo boxes...

Private Sub cmbCompany_AfterUpdate()

If Len(cmbCompany) > 0 Then
With cmbContact
.RowSource = _
"Select ContactID, FirstName, LastName " & _
"From tblContacts where CompanyID = " & cmbCompany
.Requery
.SetFocus
.Dropdown
End With
End If

End Sub

The Save/New Record button...

Private Sub cmdSave_Click()

DoCmd.Save acForm, "frmCalls"
DoCmd.GoToRecord , , acNewRec

End Sub

Either, I am not understanding the problem, or the code is wrong. Is
there any way to find out how many records were returned after
the .requery? And if so, if that number were zero, what is the best
way to handle that error? Or am I all messed up?

 

[Jeanette Cunningham]

Hi,
Me.lblTitle.Caption = Me.[ComboName.Column(1)] & " " &
Me.[ComboName.Column(2)]

The numbers next to column are to select the number of the column in the
combo that shows the first name and then the column that shows the last
name.
Combos have their column numbers starting at 0.
The first column of the combo is Column(0), the second column is Column(1)
etc.

Jeanette Cunningham

 

[Jeanette Cunningham]

Hi,
Going to a new record can be a tricky thing to get right.
As your code is written, a user can try to go to a new record anytime they
click cmdSave, but if cmbCompany is null when they click cmdSave they get
error 2105. You can stop the user trying to go to a new record when the
cboCompany is null:

Private Sub cmdSave_Click()

If IsNull(Me.cboCompany) then
'handle error here
Else
DoCmd.Save acForm, "frmCalls"
DoCmd.GoToRecord , , acNewRec
End Sub

Jeanette Cunningham

 

[negativeB]

Hi,
Going to a new record can be a tricky thing to get right.
As your code is written, a user can try to go to a new record anytime they
click cmdSave, but if cmbCompany is null when they click cmdSave they get
error 2105. You can stop the user trying to go to a new record when the
cboCompany is null:

Private Sub cmdSave_Click()

If IsNull(Me.cboCompany) then
'handle error here
Else
DoCmd.Save acForm, "frmCalls"
DoCmd.GoToRecord , , acNewRec
End Sub

Jeanette Cunningham

I know that the cascading combo box issue has been addressed quite a
bit on the various message boards and groups, but I'm stuck. I have
successfully set up a series of two cascading boxes. The filtered box
returns a first name and a last name, both from two different columns
of the same table.

However, this is where my problems start.

What I would like to do is turn the first and last name into just
NAME. (i.e. Me.lblTitle.Caption = "" & [FirstName] & " " & [LastName]
& "") Is there any way to achieve this within my SQL statement in the
VB code? Or is it a control issue?

And secondly... When the filtered combo box returns a null value or an
empty recordset, I get a 2105 runtime error when I try to go to a new
record. Here's the code.

The Combo boxes...

Private Sub cmbCompany_AfterUpdate()

If Len(cmbCompany) > 0 Then
With cmbContact
.RowSource = _
"Select ContactID, FirstName, LastName " & _
"From tblContacts where CompanyID = " & cmbCompany
.Requery
.SetFocus
.Dropdown
End With
End If

End Sub

The Save/New Record button...

Private Sub cmdSave_Click()

DoCmd.Save acForm, "frmCalls"
DoCmd.GoToRecord , , acNewRec

End Sub

Either, I am not understanding the problem, or the code is wrong. Is
there any way to find out how many records were returned after
the .requery? And if so, if that number were zero, what is the best
way to handle that error? Or am I all messed up?

Thank you for the speedy response. The first solution works great.
As for the combo boxes...

Is there any way to save the record, and move on to a new one, with
the filtered combo box null? In my example, I have cmbCompany, which
returns company names. cmbCompany filters cmbContact. If there are
no contacts for a particular company, what is the best way to handle
this error within VB? Would it be better to try to do a query?...

This little bug has been taunting me for about a week now.

 

[Jeanette Cunningham]

Glad we got one problem licked, the next one is a little trickier.
Without seeing your form it is hard to know the answer to this. I don't know
how cmbCompany relates to the rest of the form.
Is cmbCompany bound to a field in a table, if so which field in which
tables, is it a primary key? Can you post the names of all tables that
Company is involved in and the primary keys of these tables. Do you know
which are the required fields for your form, check in the tables and tell me
which fields have their required property set to yes. Please also post the
query or table that is the recordsource for this form.

Jeanette Cunningham

Hi,
Going to a new record can be a tricky thing to get right.
As your code is written, a user can try to go to a new record anytime
they
click cmdSave, but if cmbCompany is null when they click cmdSave they get
error 2105. You can stop the user trying to go to a new record when the
cboCompany is null:

Private Sub cmdSave_Click()

If IsNull(Me.cboCompany) then
'handle error here
Else
DoCmd.Save acForm, "frmCalls"
DoCmd.GoToRecord , , acNewRec
End Sub

Jeanette Cunningham

I know that the cascading combo box issue has been addressed quite a
bit on the various message boards and groups, but I'm stuck. I have
successfully set up a series of two cascading boxes. The filtered box
returns a first name and a last name, both from two different columns
of the same table.

However, this is where my problems start.

What I would like to do is turn the first and last name into just
NAME. (i.e. Me.lblTitle.Caption = "" & [FirstName] & " " & [LastName]
& "") Is there any way to achieve this within my SQL statement in the
VB code? Or is it a control issue?

And secondly... When the filtered combo box returns a null value or an
empty recordset, I get a 2105 runtime error when I try to go to a new
record. Here's the code.

The Combo boxes...

Private Sub cmbCompany_AfterUpdate()

If Len(cmbCompany) > 0 Then
With cmbContact
.RowSource = _
"Select ContactID, FirstName, LastName " & _
"From tblContacts where CompanyID = " & cmbCompany
.Requery
.SetFocus
.Dropdown
End With
End If

End Sub

The Save/New Record button...

Private Sub cmdSave_Click()

DoCmd.Save acForm, "frmCalls"
DoCmd.GoToRecord , , acNewRec

End Sub

Either, I am not understanding the problem, or the code is wrong. Is
there any way to find out how many records were returned after
the .requery? And if so, if that number were zero, what is the best
way to handle that error? Or am I all messed up?

Thank you for the speedy response. The first solution works great.
As for the combo boxes...

Is there any way to save the record, and move on to a new one, with
the filtered combo box null? In my example, I have cmbCompany, which
returns company names. cmbCompany filters cmbContact. If there are
no contacts for a particular company, what is the best way to handle
this error within VB? Would it be better to try to do a query?...

This little bug has been taunting me for about a week now.

 

[negativeB]

Glad we got one problem licked, the next one is a little trickier.
Without seeing your form it is hard to know the answer to this. I don't know
how cmbCompany relates to the rest of the form.
Is cmbCompany bound to a field in a table, if so which field in which
tables, is it a primary key? Can you post the names of all tables that
Company is involved in and the primary keys of these tables. Do you know
which are the required fields for your form, check in the tables and tell me
which fields have their required property set to yes. Please also post the
query or table that is the recordsource for this form.

I have three tables. tblContacts, tblCompany, and tblCalls, with the
following values.

tblContacts:
ContactID - AutoNumber - PK
FirstName - Text
LastName - Text
CompanyID - Number

tblCompany:
CompanyID - AutoNumber - PK
CompanyName - Text
ContactID - Number

tblCalls:
CallID - AutoNumber - PK
CallDate - Date/Time
ContactID - Number
CompanyID - Number

I have established all of the requisite relationships with no bugs.
The form that my combo boxes sit on is my calls form - frmCalls.

Here is the SQL behind the RecordSource for frmCalls.

SELECT tblCalls.*, tblCompany.PhoneNumber, tblCompany.CompanyName,
tblContacts.FirstName, tblContacts.LastName
FROM tblContacts INNER JOIN (tblCompany INNER JOIN tblCalls ON
tblCompany.CompanyID = tblCalls.CompanyID) ON tblContacts.ContactID =
tblCalls.ContactID;

What I'm trying to do is this. When one of my distributors makes a
call, I want him/her to document it in frmCalls. I want them to
choose from a list of companies, and if applicable, choose from the
filtered list of contacts. However, if they only call the HR
department, no information would be needed in cmbContact. When this
happenes, I would like for the form to save the information in
cmbCompany, and not care that cmbContact is null.

These two fields are not required, but I have error traps in place on
my save button, as well as in the AfterUpdate event of the form. Is
the problem in my SQL?... Access wrote that statement for me, but
looking at it now, I realize that it isn't as efficient as it could
be.

 

[Jeanette Cunningham]

Hi,
I have tried your setup with the 3 tables as you described and the form.
Here is how I made it work.
In the relationship window I set up the tables like this:
tblCompany 1 to Many with tblCalls
tblCompany 1 to Many with tblContacts
I made the recordsource for the form tblCalls.
The form has:
txtbox for CallID
txtbox for CallDate
combo to look up Company Name with CompanyID
combo to look up Contact with ContactID

There were no issues with going to a new record.
If there were no contacts at a particular company, the Contact combo showed
a blank dropdown list.
Hope this helps

Jeanette Cunningham

 

[negativeB]

Hi,
I have tried your setup with the 3 tables as you described and the form.
Here is how I made it work.
In the relationship window I set up the tables like this:
tblCompany 1 to Many with tblCalls
tblCompany 1 to Many with tblContacts
I made the recordsource for the form tblCalls.
The form has:
txtbox for CallID
txtbox for CallDate
combo to look up Company Name with CompanyID
combo to look up Contact with ContactID

There were no issues with going to a new record.
If there were no contacts at a particular company, the Contact combo showed
a blank dropdown list.
Hope this helps

Jeanette Cunningham

Worked great...

Thanks for all your help.