Help~ Update a Text Box associated with a selected Combo box

[faiyeah]

Private Sub PubName_AfterUpdate()

Dim sql As String
sql = "select PubCode from Publisher where PubName = '" &
PubName.Value & "';"
Set rst = CurrentDb.OpenRecordset(sql)
MsgBox sql
If Not rst.BOF Then
rst.MoveFirst
End If
PubCode.Value = rst(0) <-- error

End Sub

PubCode is a text box and PubName is a combo box.
I have a table like this:
Publisher: PubCode, PubName

and my form is like that...
If I selected a PubName in PubName combo box, I want to change the
PubCode correspondingly, since they are actually refering the same
tuple.

But When I use the above code...
run time error 3331 occur sayign that

To make change in this field, first save the record.

I am getting dizzy~~
Can anyone help?
Thx~

[Bob Quintal]

"faiyeah" <fa********@gmail.com> wrote in
news:11*********************@j33g2000cwa.googlegro ups.com:

Private Sub PubName_AfterUpdate()

Dim sql As String
sql = "select PubCode from Publisher where PubName = '" &
PubName.Value & "';"
Set rst = CurrentDb.OpenRecordset(sql)
MsgBox sql
If Not rst.BOF Then
rst.MoveFirst
End If
PubCode.Value = rst(0) <-- error

End Sub

PubCode is a text box and PubName is a combo box.
I have a table like this:
Publisher: PubCode, PubName

and my form is like that...
If I selected a PubName in PubName combo box, I want to change
the PubCode correspondingly, since they are actually refering
the same tuple.

But When I use the above code...
run time error 3331 occur sayign that

To make change in this field, first save the record.

I am getting dizzy~~
Can anyone help?
Thx~

Since pubname and pubcode both exist in the same table, put them
both in the combobox. Make the pubcode column = 0", so it's not
visible. Then in the combo box after update, set the textbox
value to the value in the hidden column

PubCode.Value = combobox.column(n) where is the number of the
column, counted left to right.

--
Bob Quintal

PA is y I've altered my email address.

[faiyeah]

Sorry Bob, I am still not so clear about your idea...
could you be more detail??

Should I create a combobox, with pubname and pubcode and set it
invisible?
and then... what should I do??

Thank you for you great help anyway~

[Bob Quintal]

"faiyeah" <fa********@gmail.com> wrote in
news:11**********************@t31g2000cwb.googlegr oups.com:

Sorry Bob, I am still not so clear about your idea...
could you be more detail??

Should I create a combobox, with pubname and pubcode and set it
invisible?
and then... what should I do??

Thank you for you great help anyway~

You already have a combobox, PubCode is a text box and PubName is a combo box

Modify the existing combobox.

--
Bob Quintal

PA is y I've altered my email address.

[faiyeah]

Thank you.
I have solved it~~