-2

I am trying to have a text box show the value that is associated with a certain value. I have a table with 2 columns, [Error_Code] and [Type]. I want to have a combobox with Error_Codes (which works) and whenever a selection is made, the [Type] will appear in the textbox. For some reason, I have been testing this to a msgbox, will change later. 

Dim rst As DAO.Recordset
Dim ERtype As String

Set rst = CurrentDb.OpenRecordset("select * from Error_Table where [Code]='" & Me!cb_Error.Value & "'")

MsgBox "testing: " & rst!Type_

rst.Close
Set rst = Nothing

This seems to return run-time error 3265, item not found. I have looked this up and my table name and fields are correct. Any ideas what is going on?

jrussin
  • 79
  • 1
  • 7
  • 1
    Could use DLookup() domain aggregate instead of recordset. Or maybe just include table in form or report RecordSource. Or build a multi-column combobox on form. – June7 Apr 17 '18 at 21:00
  • 1
    VBA really should not be necessary just to display the Type value. And even if you do use it to populate an unbound textbox, the same value will show for all records so unless form is in Single View, the output will be unsatisfactory. If you want to save the Type (why?), build multi-column combobox then expression in VBA can reference column that holds the Type value. – June7 Apr 17 '18 at 21:12
  • [Debugging VBA Code](http://www.cpearson.com/excel/DebuggingVBA.aspx) -- Which line gives the error? -- Is the field name really `Type_` with the underscore? – Andre Apr 17 '18 at 21:48
  • It is the line: MsgBox "testing: " & rst!Type – jrussin Apr 17 '18 at 21:52
  • Also, it does not have the underscore, that was a typo, still no result. – jrussin Apr 17 '18 at 21:53
  • I tried the following, Dim ErrType As String ErrType = (DLookup("Type_Err", "Error_Table", "Code_Err= '" & Me.cb_Error & "'")) Me.txt_Type = ErrType – jrussin Apr 17 '18 at 22:14
  • Still gives an error, invalid use of null – jrussin Apr 17 '18 at 22:15
  • If you put a debugger on those lines do any of the values show as null? Either `Me.cb_Error` or `ErrType`. Is it possible there is no Type value for the code you are looking up? – Zaider Apr 19 '18 at 12:59

1 Answers1

0

I'm not an expert in this, but I think it should be:

Me.cb_Error.Value

Not:

Me!cb_Error.Value

Use the dot operator, not the exclamation mark.

Elletlar
  • 3,136
  • 7
  • 32
  • 38
user14500986
  • 1