Bookmark problem

[poporacer]

I have a database with 2 tables. One table is the specifics for a person. The other table is for tracking incidents with these people. I have a form (frmEdit)that has a combo box with the person's ID in it and you select the ID and the form should populate with the data on that person. The first problem I have is I use this code to update the form:

Me.RecordsetClone.FindFirst "[ID] = " & Me.cmbCDC 'Combo box with ID

Me.Bookmark = Me.RecordsetClone.Bookmark

rs.FindFirst "[ID] = " & Me.cmbCDC
rs.MovePrevious ' I had to put this in because it was always one item forward of the data
'The next code enters the data to the form from the recordset.
txtName = rs.Fields("Name")
txtEthnic = rs.Fields("Ethnicity")
txtDOB = rs.Fields("DOB")

If you select the first Person in the list, the form populates properly. If you choose any other person you get an error that the value entered is not valid for this field.

 

[RuralGuy]

Why not add in:

If Not Me.RecordsetClone.NoMatch Then
    Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Unable to locate [ID] = '" & Me.cmbCDC & "'"
End If

...and see what is going on. Is Me.cmbCDC a numeric value or text? You are treating it as if it is numeric.

 

[poporacer]

thanks so far

Me.cmbCDC IS a text field....that is probably the root of the error...how do I treat it as such?

 

[RuralGuy]

Me.RecordsetClone.FindFirst "[ID] = '" & Me.cmbCDC & "'"
or
Me.RecordsetClone.FindFirst "[ID] = " & Chr(34) & Me.cmbCDC & Chr(34)
or
Me.RecordsetClone.FindFirst "[ID] = """ & Me.cmbCDC & """"

 

[poporacer]

Still no cigar

I tried changing the code to the different ones and I got a "Data type mismatch" error....the first code I wasn't sure of the quotes...here is how it looked.

Me.RecordsetClone.FindFirst "[ID] = '" & Me.cmbCDC & "'"
^ ^
single quote double quote Double single double

I got the same error with all three codes....why is the ID one ahead of the actual table?

 

[RuralGuy]

That error indicates to me that the cmbCDC ComboBox is bound to a numeric field. You are not per chance using look up fields in your table are you?

 

[poporacer]

Numeric field?

cmbCDC is populated from a query. I assume that it is a lookup field then? Here is what I have....I have two tables
tblIncident has the following fields:
ID
Log# 'there will be several of the same log# entries
CDCNum 'there will be only one CDCNum per Log# but could in different Log#s

The rest of the fields are data that is the specific data for this log# and the corresponding CDCNum.

The other table is used for the specific data that is for the CDCNum so that I am not duplicating the data in tblIncident. The following is the structure for this table:

ID
CDCNum
Name
DOB
etc.....

The form has two combo boxes, cmbLogNum and cmbCDC. cmbLogNum is populated with a DISTINCT query so that it only lists one Log number for each incident. After that is selected, the combo box cmbCDC is populated with the CDC numbers of the people involved in that incident. This will get me to the exact record to edit. After the CDC number is selected, the form is populated. The form has two parts. One part of the form, the fields are bound to tblIncident and I need the text boxes filled in by the selection (me.recordsetclone) and the other part of the form is the corresponding information to the person involved....name, DOB, etc. (findfirst query)

Am I going about this wrong?
thanks in advance for your speedy and helpful replies.

 

[RuralGuy]

You need to open the RecordSource of the cmbCDC in design mode and look at the Lookup tab for the bound field to see if it is a Textbox. If it is a ComboBox then you need to re-read the link I put in my previous post and then change it to a TextBox.

 

[boblarson]

You need to open the RecordSource of the cmbCDC in design mode and look at the Lookup tab for the bound field to see if it is a Textbox. If it is a ComboBox then you need to re-read the link I put in me previous post and then change it to a TextBox.

Just something to help with this:

 

[RuralGuy]

You know Bob, those picture examples you come up with are so useful I may just have to spring for a copy of SnagIt. That's beautiful.

 

[poporacer]

A little confused

The row source type of cmbCDC is Table/Query...I have a query to populate the cmbCDC. I didn't find a recordsource or anything that mentions lookup tab. I know on tables there is that option for lookup and all text fields are set to Textbox. I guess I am not looking in the right area.

WOW you are quick to respond too!
Thanks a million

P.S. While I was posting, BobLarson responded...I checked my tables and all fields are set to Text box with the exception of Date fields and the autonumber field

 

[boblarson]

You know Bob, those picture examples you come up with are so useful I may just have to spring for a copy of SnagIt. That's beautiful.

I HIGHLY recommend it. It is one of the best $39.99 I ever spent. I've definitely gotten my money's worth.

 

[RuralGuy]

Did you ever just add the code I posted in post #2 to see if the .FindFirst is successful?

 

[poporacer]

I didn't before but I just did....I still get the "value isn't valid for this field" error. I did not get the message box either.

 

[poporacer]

After a closer look and adding the code, and looking at the posts, I might not have been clear where the error message comes from. I get the error message in the following code:
rs.FindFirst "[ID] = " & Me.cmbCDC
rs.MovePrevious ' I had to put this in because it was always one item forward of the data
'The next code enters the data to the form from the recordset.
**the following line is where it appears to err**
txtName = rs.Fields("Name")
txtEthnic = rs.Fields("Ethnicity")
txtDOB = rs.Fields("DOB")

 

[RuralGuy]

Change your .FindFirst back to the numeric format.
Me.RecordsetClone.FindFirst "[ID] = " & Me.cmbCDC

 

[poporacer]

OK done...same error

 

[RuralGuy]

After a closer look and adding the code, and looking at the posts, I might not have been clear where the error message comes from. I get the error message in the following code:
rs.FindFirst "[ID] = " & Me.cmbCDC
rs.MovePrevious ' I had to put this in because it was always one item forward of the data
'The next code enters the data to the form from the recordset.
**the following line is where it appears to err**
txtName = rs.Fields("Name")
txtEthnic = rs.Fields("Ethnicity")
txtDOB = rs.Fields("DOB")

You should not need this .FindFirst. There is something going wrong with the first Me.RecordsetClone.FindFirst that we need to fix first.

 

[RuralGuy]

I'm kind of lost now. How about posting the whole SubRoutine starting with Private and ending with End Sub and put it inside of the code tags. Press the "#" key to get the code tags. Then we'll both have a complete set of code to debug together.

 

[poporacer]

Try this...Now I am confused

OK...I decided to set it up and post the DB....In setting up data this is what it appeared....at first. Everything works fine if you select the first item in the cmbCDC combo box...then I added some data and it didn't work for the new incident, but some worked for the first and second items in the combo box....Attached is the database
Open frmEdit and select an incident and then select a CDC num from the CDC combo box to see what I am talking about.