text box doesn't reflect the change on combo box

[hu5]

I set up a form with a FindRecord combo box, which shows 2 fields, first one is ID, 2nd one is type. and also has a text box name: type. and I have a sort query for rowSource property for this combo box.

There are duplicate ID with different type. for example:
one 410v is a certificate, another 401v is a report.
When select this ID in the combo box, the type textbox shows the first type: cert, when select the 2nd dup ID, the type textbox doesn't show the corresponding type, the cert remains, it should change to Report. What did I do wrong? Please help. Thanks.

 

[TheAceMan1]

How are ya hu5 . . . . .

[Red]I'm sure you know duplicate ID's is a bad Idea!?[/red]

In theAfterUpdate of the CB try this:

[blue]Me![purple]TextBoxName[/purple] = Me![purple]ComboboxName[/purple].Column(1)[/blue]

See Ya! . . . . . .

 

[ehsguy77]

Aceman,

Can Me!TextBoxName = Me!ComboBoxName.Column(1) be used in the AfterUpdate event of a CB to enter text into a bound text box? I've been unsuccessful so far. It tells me something about there being no macro with the expression I typed. Any ideas?

 

[TheAceMan1]

ehsguy77 . . . . .

Yes it can be used to assign to bound or unbound.

Are you saying [blue]Me!TextBoxName = MeComboBoxName.Column(1)[/blue] is giving you this error?

I'm sure you know [blue]TextBoxName[/blue] & [blue]ComboBoxName[/blue] are placeholders for the actual names of the controls.

See Ya! . . . . . .

 

[hu5]

Hi, TheAceMan1

Thanks for your help, but I still have problem with the statement, Me!Type = Me!FindRecord.Column(1), depends on where I put it in the code, it either give me runtime error '3020': "update or cancelUpdate without Addnew or Edit" or keep asking me if I want to save record when I select in comboBox which is named FindRecord. and the type is the text box. Thanks again.



Private Sub FindRecord_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SRM No] = '" & Me![FindRecord] & "'"
Me.Bookmark = rs.Bookmark

Me!Type = Me!FindRecord.Column(1)


End Sub

 

[TheAceMan1]

hu5 . . . . .

If [blue][SRM No][/blue] is numeric, try this:

[blue]Private Sub FindRecord_AfterUpdate()
 ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SRM No] = " & [purple][b]Val([/b][/purple]Me![FindRecord][purple][b])[/b][/purple]
    Me.Bookmark = rs.Bookmark

 Me!Type = Me!FindRecord.Column(1)[/blue]

End Sub

See Ya! . . . . . .

 

[hu5]

TheAceMan1,

[SRM No] is not numeric, so it gives me a "data type mismatch" error. Thanks anyway.

 

[TheAceMan1]

hu5 . . . .

Replace:
rs.FindFirst "[SRM No] = " & Val(Me![FindRecord])
With:
rs.FindFirst "[SRM No] = " & chr(34) & Me!FindRecord.Column(0) & chr(34)

See Ya! . . . . . .

 

[hu5]

TheAceMan1

Thanks very much for your effort trying to help me out.

This code still not working in selecting Dup SRM No., sometimes during selection in combo box, it will ask "delete record, y,n?".
most of the time, if select unique SRM No, it works.

Below is my table format, for the Dup SRM No, the TYPE text box on the form only picks up the first type, never shows the 2nd type, I don't have the option to make SRM No. a unique ID. I can try to make SRM No. a numeric field, and move letter to a new field, and see if that help.

SRM No Type
1494 (N) Cert
1494 (N) Report
188 (N) MSDS
1951b Cert
2514 C-Cert
2751 (V) C-Cert
2751 (V) MSDS