Help with code

[jfuller]

Hi All,
So i managed to get this code to work with my limited VBA knowlege.
Private Sub RecordNumber_AfterUpdate()
Me.RecordID = "DQC-R-" & Format(Forms![Record Inventory]![CreatedDate], "mmddyyyy") & Me.Technician & "-" & Me.RecordNumber
End Sub

However, there is one little thing that is keeping it from being what i need. the Me.Technician part of the code is reporting the primary key of the data instead of the actual data, which i want. I have a field on a form named TechInitals, which is a list box created from a table containing information for all the technicians in my dept. I need vba to display their initials, instead of the primary key field.
Any help with this?

[Bob Fitz]

Hi jfuller

You could try this:

 
Me.RecordID = "DQC-R-" & Format(Forms![Record Inventory]![CreatedDate], "mmddyyyy") & Me.TechInitals.Column(1) & "-" & Me.RecordNumber

I have assumed that the listbox "TechInitals" has 2 columns, the first one being hidden but holding the numeric "ID" of the Technician.
The columns of listboxes and comboboxes are indexed (numbered) starting with 0. So the 2nd column would be "Column(1)".

[jfuller]

Thanks Bob, i see where you are going, but the field TechInitials is actually from another table. I used a query to populate the listbox for that object. So i think in someway, i have to refer to that table, or the query to get the format that i want?

[jfuller]

You were right, all i had to do was add column(1). Thanks!!!

[Bob Fitz]

Hi

You keep calling "TechInitials" a field but it is not because forms do not have Fields. They do have controls. If "TechInitials" is a listbox or a combobox then I think the code I posted should work but it's up to you to whether you even try it.

[Bob Fitz]

Hi

Glad to know that it is now working as required