Two Drop Downs To Choose 1 Record

I have a form in an Access database that has two options for looking up a person. Either by login ID or by name.

So, i have cboName and cboID.

Both are linked to the same table, but different fields.

That way, if someone only knows their ID ,they can use the cboID to look it up, or enter it. OR they can use the cboName to look up the name or enter it.

However, what I want to do is link the two combo boxes so that when they choose a name, the equvalent ID shows up in cboID, and vice versa.

I had tried this, but it is not working:


Private Sub cboName_Click()

Dim daoID As DAO.Recordset

Set daoID = CurrentDb.OpenRecordset("SELECT tblLIST_CS_EMP.L_ID FROM tblList_CS_EMP WHERE Full_Name='" & Me.cboName.Text & "'")

If IsNumeric(daoID(0)) Then
cboID.SetFocus

cboID.Text = daoID(0)
End If

End Sub


I am getting an error that the "Macro or Function set to the BeforeUpdate or ValidationRule property for this field is preventing Access from saving the data in the field."

I don't have anything in the BeforeUpdate or ValidationRule.

IS there an easier way to do what I want to do, or am I on the right track with the code?

I believe your problem is because you're trying to assign a value to the cboid when the cboid is already linked to a different value. What if you did something like this.

If they choose to look them up by name, then disable the cboid and display the id in a different textbox or label....and vice versa. Just a thought.

I just tried that, I left the cboName and linked that to the table holding the employee info.

Then I have a txtID control that is not linked to anything.

I use this code:

Private Sub cboName_Click()

Dim daoID As DAO.Recordset
Dim strID As String

Set daoID = CurrentDb.OpenRecordset("SELECT tblLIST_CS_EMP.L_ID FROM tblList_CS_EMP WHERE Full_Name='" & Me.cboName.Text & "'")

If IsNumeric(daoID(0)) Then
txtID.SetFocus
strID = daoID(0)
txtID.Text = strID
End If

End Sub


But i am still getting the following error:
"Macro or Function set to the BeforeUpdate or ValidationRule property for this field is preventing Access from saving the data in the field."


I believe your problem is because you're trying to assign a value to the cboid when the cboid is already linked to a different value. What if you did something like this.

If they choose to look them up by name, then disable the cboid and display the id in a different textbox or label....and vice versa. Just a thought.

I'm sure this application is running within Access Application, right?

Then use the value property not the text property

Private Sub cboName_Click()

Dim daoID As DAO.Recordset

Set daoID = CurrentDb.OpenRecordset("SELECT tblLIST_CS_EMP.L_ID FROM tblList_CS_EMP WHERE Full_Name='" & Me.cboName.Text & "'")

If IsNumeric(daoID.Fields(0).Value) Then
txtID.Value = daoID.Fields(0).Value
End If
daoID.Close
Set daoID = Nothing
End Sub

btw: you should first check if the recordset is EOF and BOF before accessing its value.

If the combobox is bind to a table, you can directly access the ID field by passing the combobox recordset property to daoID instead of opening it again.

i.e.

'declaration code
Set daoID = Me.cboName.Recordset
'the rest of the code here....

Yes, I am creating a form in an Access database.

Using the value property's worked perfectly...thanks!

Question though...why does using the value over the text property make the difference?

OK, checking the EOF and BOF makes sense.

I figured since the cboName control was already accessing the table that their would be an easier/better ay then opening onther conection...but unsure how to do that.

I'm sure this application is running within Access Application, right?

Then use the value property not the text property

Private Sub cboName_Click()

Dim daoID As DAO.Recordset

Set daoID = CurrentDb.OpenRecordset("SELECT tblLIST_CS_EMP.L_ID FROM tblList_CS_EMP WHERE Full_Name='" & Me.cboName.Text & "'")

If IsNumeric(daoID.Fields(0).Value) Then
txtID.Value = daoID.Fields(0).Value
End If
daoID.Close
Set daoID = Nothing
End Sub


btw: you should first check if the recordset is EOF and BOF before accessing its value.
if the combobox is bind to a table the you can directly access the ID field by passing the value of combobox recordset property to daoID instead of opening it again.

Question though...why does using the value over the text property make the difference?

I don't really have a good answer to this question, maybe the VBA'ers can answer your question

OK, checking the EOF and BOF makes sense.

it is really important that you check the recordset for possible EOF and BOF before retrieving any values from it or it will throw an error.

I figured since the cboName control was already accessing the table that their would be an easier/better ay then opening onther conection...but unsure how to do that.

I already told you about that, see my last post, but just give you more insight.

Private Sub cboName_Click()

Dim daoID As DAO.Recordset

Set daoID = Me.cboName.Recordset
If IsNumeric(daoID.Fields(0).Value) Then
txtID.Value = daoID.Fields(0).Value
End If
daoID.Close
Set daoID = Nothing
End Sub

OK....makes a little sense...but I am missing something.

In this line:

txtID.Value = daoID.Fields(0).Value


You have daoID.Fields(0).Value which equates to the L_ID field in the table...

But how? The L_ID is not the first field...

The reason this has come up, is because in another combo box I did the same thing, but the wrong field's value is being obtained. I tried changing the index

Private Sub cboCode_Click()

Dim daoDesc As DAO.Recordset

Set daoDesc = Me.cboCode.Recordset

txtDesc.Value = daoDesc.Fields(1).Value

daoDesc.Close
Set daoDesc = Nothing
End Sub


and it causing the following error: "Item not found in this collection".

I also tried:

Private Sub cboCode_Click()

Dim daoDesc As DAO.Recordset

Set daoDesc = Me.cboCode.Recordset

txtDesc.Value = daoDesc.Fields("Desc").Value

daoDesc.Close
Set daoDesc = Nothing
End Sub


I don't really have a good answer to this question, maybe the VBA'ers can answer your question

it is really important that you check the recordset for possible EOF and BOF before retrieving any values from it or it will throw an error.

I already told you about that, see my last post, but just give you more insight.

Private Sub cboName_Click()

Dim daoID As DAO.Recordset

Set daoID = Me.cboName.Recordset
If IsNumeric(daoID.Fields(0).Value) Then
txtID.Value = daoID.Fields(0).Value
End If
daoID.Close
Set daoID = Nothing
End Sub

It means that there's no desc field in the recordset, If this method is working fine in cboName (Combobox Control), perhaps the problem is in your cboCode, try to check the name of each field in the cboCode.Recordset property, by looping through the field count of the recordset i.e.

'in the click event of the cboCode combobox

Dim i As Integer
set adoDesc = cboCode.Recordset
For i = 0 to adoDesc.Fields.Count - 1
MsgBox adoDesc.Fields(i).Name
Next


btw: I'm a bit confuse, in your ealier post, you are using cboName and now you have cboCode, which I think, use the same table. If these 2 controls are using the same table, then cboName is enough, you just need to select all the information that you want to retrive from that table.

check this link and read the entire thread
http://www.xtremevbtalk.com/showthread.php?t=195468

AHHH...OK, i figured it out....

In the cboCode RowSource properry I just had:
SELECT tblLIST_EXC.Code FROM tblLIST_EXC;

Because I was wanting the value of Code to show in the drop down box.

What I changed it to was this:
SELECT tblLIST_EXC.Code, tblLIST_EXC.Desc FROM tblLIST_EXC;
Now it works fine.

So, I was assuming that by attaching the combo boxes to one field in a record still stored all the other fields.

Basically what I am doing is creating a log for phone exceptions.

cboName pulls it's data from the table of employees.
cboCode pulls it's data from the table of possible codes.

when I use the following way (for both combos), I am coming into some weird problems:

Private Sub cboCode_Click()

Dim daoDesc As DAO.Recordset

Set daoDesc = cboCode.Recordset

txtDesc.Value = daoDesc.Fields(1).Value

daoDesc.Close
Set daoDesc = Nothing

txtDFrom.SetFocus
End Sub


What's happening is when I first choose one of the dropdow choices, the value that appears in the txtDesc or txtID are from the very first record. Even if I don't choose that one.

But, when I go back and choose from them again ,then the values in txtCode and txtID match with the value showing in cboName and cboCode.

Hope I am explaining that well...any ideas?

when I use the following way (for both combos), I am coming into some weird problems:

Private Sub cboCode_Click()

Dim daoDesc As DAO.Recordset

Set daoDesc = cboCode.Recordset

txtDesc.Value = daoDesc.Fields(1).Value

daoDesc.Close
Set daoDesc = Nothing

txtDFrom.SetFocus
End Sub


What's happening is when I first choose one of the dropdow choices, the value that appears in the txtDesc or txtID are from the very first record. Even if I don't choose that one.

But, when I go back and choose from them again ,then the values in txtCode and txtID match with the value showing in cboName and cboCode.

Hope I am explaining that well...any ideas?

Strange indeed......
Does it happen on both combo controls? if it does happen on both controls you can try the other method using the SELECT statement.

read this
http://www.xtremevbtalk.com/showpost.php?p=877890&postcount=6

BTW: the code is working fine in my PC ;)

Yeah, it's happening on both...weird....so I went back to using the SQL statement...

Thanks for all your help!