Hi Maurice,
It sounds like you have the basic idea. I'm going to give you a bit more
background - just because I'd rather you have too much info than not enough
and maybe it will help you see something differently.
tblClientCo tblAdjuster
ClientCoID (PK) Autonumber AdjusterID (PK) Autonumber
Name ClientCoID (FK) Number
City FirstName
State LastName
Zip Phone
CompanyType Extn
I also have qryInsuranceCo that filters only the records where CompanyType =
InsuranceCo which is the RowSource for cboInsuranceCoID. I have the same
code on this combo box on the NotInList event and it works perfectly. In
both of these instances, the bound field is the ID (Autonumber) but the box
is set so that it only displays the Name field. Here is the code that
filters for cboAdjusterID:
Private Sub cboInsuranceCoID_AfterUpdate()
On Error Resume Next
cboAdjusterID.Visible = True
cboAdjusterID.RowSource = "Select tblAdjuster.AdjusterID," & _
"tblAdjuster.AdjusterFirstName & ' ' & tblAdjuster.AdjusterLastName
" & _
"FROM tblAdjuster WHERE tblAdjuster.CompanyID =
cboInsuranceCoID.Value " & _
"ORDER BY tblAdjuster.AdjusterID"
End Sub
Here is the code where it works correctly - perhaps you can see a
difference...
Private Sub cboInsuranceCoID_NotInList(NewData As String, Response As Integer)
If MsgBox(NewData & " is not in the list." & vbCrLf & "Would you like to
add " & NewData & "?", vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
Me.cboInsuranceCoID.Undo
DoCmd.OpenForm "frmClientCo", , , , acFormAdd, acDialog
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst [InsuranceCoID] = " & NewData & """
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboInsuranceCoID.Undo
Response = acDataErrContinue
End If
End Sub
The person that gave me the code told me that for the .FindFirst line the
quotes needed to be different depending on whether it's text or number. I
wasn't sure if that applied to the bound field (which would be an autonumber)
or the input data (which would be text - FirstName and LastName) so I played
with them until I got it right but none of them seem to work here. Thanks
again for your time and help with this - you are very kind and patient!! : )
Maurice said:
Pam,
Here's what I tried. Please feel free to correct me if i'm not getting the
picture right.
I created a table with two combo's on it. The second one is depended on the
first one. When the second one hasn't got the option you are looking for you
want to add this option to the second combo right?
Does this also count for new records? Is the InsuranceCoID an autonumber
generated when you create a new record. If not it could be that your
'openargs' option is null.
Anyway I added the combo's and the second one is linked to the AdjusterID
field of the table it resides in. The source of the combo however is the
table which has the options to store the added values (could be contacts or
something like that).
When I add a record which isn't there it asks me if i want to add it. I
prompt yes and am taken to the Adjuster form which is bound to the table
which holds the records added to the combobox. When I enter the added value
and close the form I put me.cboAdjusterID.requery to the code to make sure
the combo is properly refreshed. The add value is displayed as expected.
summary: the second combo is bound to the field in the designated table
where you store the InsuranceID. The combo has a rowsource type set to
table/query which is the table where you retrieve the Adjusters. The form you
open has the same source but with all the fields so you can complement these.
At the end make sure you requery the second combo so the added record will
be part of the source of the combo.
Hope i got the picture. The only thing I might need to know is what the
source of the second combo is (you adjusters). Is this a table? Tell me what
fields are in there.
--
Maurice Ausum
Pamela said:
I tried the commenting out suggestion but it didn't work. When I closed the
popup form, cboAdjusterID was blank and Focus was put back to
CboInsuranceCoID (from which cboAdjusterID is cascaded) and when I tab to the
cboAdjusterID, the entry in CboInsuranceCoID disappears . I'm using this same
structure of code on 2 other combo boxes on my form and they work perfectly.
So it really stumps me as to why this one is getting errors. The end result
I would like is to enter in an Insurance Co in one cbo, have it filter the
Adjusters in the next cbo and if it's a new name, open another form to add it
in. If you have another way to do it, please let me know...I'm completely
open!! Thanks!!
Maurice said:
On first sight it looks like Newdata is filled with the cboAdjusterID.
However the first thing you do is me.cboAdjusterID.undo. So my guess would be
that there is no value to do the searching anymore.
Try commenting out this line and see if it works then (put an apostrophe in
front of the line).
I'll set up the same situation and see if i can mimic it. I'll get back to
you on this one.
--
Maurice Ausum
:
Here it is:
Private Sub cboAdjusterID_NotInList(NewData As String, Response As Integer)
If MsgBox(NewData & " is not in the list." & vbCrLf & "Would you like to add
" & NewData & "?", vbQuestion + _
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then
Me.cboAdjusterID.Undo
DoCmd.OpenForm "frmAdjuster", , , , acFormAdd, acDialog,
Me.InsuranceCoID
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[AdjusterID] = " & NewData
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboAdjusterID.Undo
Response = acDataErrContinue
End If
End Sub
Thanks for your continued help....
:
Pamela,
Could you post your code so we can try to sort out what the problem might
be...
--
Maurice Ausum
:
Thanks for the tip, but now I got this error:
Syntax Error (missing operator) in expression
Any other suggestions?? If you need any other info, please let me know!
Thanks!!
:
My guess would be that the id field should be treated as a numeric value.
Try: .FindFirst "[AdjusterID] = " & NewData
and see if that helps.
--
Maurice Ausum
:
I have cboAdjusterID on frmClaim that opens frmAdjuster on the NotInList
event to add the new entry. Someone here helped me with the code but now I'm
getting a Data Type Conversion Error. When I debug, I am taken to this line
of code:
.FindFirst "[AdjusterID] = " & NewData & ""
A little more background: the combo box is formatted to show the
concatenated text of FirstName and LastName but the ID field is the bound
one. I'm concerned that this may be the root of the problem. When the user
would be entering the NotInList data into the box, they would be entering
both FirstName and LastName which upon correctly adding to the frmAdjuster
would be broken down into their respective fields. Any suggestions on how
to fix this would be wonderful!!! Thanks!
