Record lookup and autocreate new record

[SubMatrix]

Ok, so I have a form that has a drop-down box named "Name_Select" used to select personnel listed in a table. In the After Update event I have a macro that does:

SearchForRecord
="[Name_Last_First)] = " & "'" & [Screen].[ActiveControl] & "'"

Basically, the drop-down box references the table and lists all the personnel, after a name is selected, the actual field that holds the name data (which is hidden) is populated with the drop-down choice and all the other corresponding fields load as well. This has worked fine for this purpose, however...

The problem I am encountering is that I have another table based off of the personnel table with 1 record per person. What I want is for the drop-down box to reference the table1 and list all the names (which it does) and then to load the corresponding record from table2 if it exists (which it also does) and if no record exists for that person, to create one with empty fields (which it half does). Currently, if a person is selected that has no data, it leaves the data from the previous person selected. The data can still be edited, and then saved, and in that matter does what I want it to do by creating a new record. But to the user, it can be quite confusing when selecting a new person and seeing data already there.

Additionally, if possible, I would prefer to move all of this code away from a macro and into VBA. Macros were great before I had any idea how VBA worked, but now that I have a mild grasp on it, I find that it is much easier to troubleshoot and modify VBA code than a macro. Plus, more than once I've spent a significant amount of time trying to figure out why something was happening only to discover some rogue macro in some obscure event that I had forgotten about.

I appreciate any help anyone can offer.

 

[SubMatrix]

Possibly to save some time, I found out how to place the code in vba, I just need a way to see if the record exists or not. Here's what I have so far:

Private Sub Name1_AfterUpdate()
Dim SaveName As String
SaveName = Name1.Value

If the value of Name1 does not exist in table2 then
DoCmd.GoToRecord , , acNewRec
Name1.Value = SaveName
Name2.Value = SaveName
Else
DoCmd.SearchForRecord acActiveDataObject, "", acFirst, "[Name_Last_ First] = " & "'" & [Screen].[ActiveControl] & "'"
End If
End Sub

So basically I capture the person's name and then try to compare it to the table to see if a record exists. If it doesn't, then it creates a new record which should automatically have blank fields and autopopulates the name that was selected, leaving the user to fill in the remaining fields. If the record does exist, then it just goes straight into loading that record.

I've searched for how to check if a record exists but the code I'm seeing looks way over my head.

 

[SubMatrix]

Ok, well it took me trying roughly 20 variations of "" and [] but I got it to work:

If IsNull(DLookup("Name_Last_First", "table2", "[Name_Last_First] = [Forms]![Form1]![Name1]")) Then

Hope that at least this will help someone else in the future.