Software: Access 2003
Objective: I have two tables in my database. I want to be able to execute code in a button on a form that verifies that a record in table2 exists via user input with 'InputBox'. If the record doesn't exist, then I would like to have the record copied from table1 to table2.
What I have currently done: I currently use 'FindFirst' along with 'InputBox' to verify if a record exists in a table, however, I am unable to figure out the code to switch to another table and check if the record exists there.
Additionally, I don't know how to copy specified fields from one table to another.
If any of this causes any confusion then let me know and I'll be happy to try and better explain.
Here's some code based on an Employees2 Table, which would parallel your Table2 scenario. This code should do everything that you had requested in your Post. I'll just Post the code for now, since it is getting past my bedtime, but should you have any questions whatsoever, please feel free to list them, and either myself or one of the other fine gentlemen (yes NeoPa, you too! (LOL)), will be happy to assist you.
-
Dim MyDB As DAO.Database
-
Dim MyRS As DAO.Recordset
-
Dim varLastName As Variant
-
Dim strMsg As String
-
Dim intResponse As Integer
-
-
Set MyDB = CurrentDb
-
Set MyRS = MyDB.OpenRecordset("Employees2", dbOpenDynaset)
-
-
'varLastName = Trim(InputBox("Enter a Last Name to Search For."))
-
'OR
-
'Forgive my confusion, but why not simply read the value from an
-
'existing Text Box on the Form
-
varLastName = Me![txtLastName]
-
-
If Len(varLastName) = 0 Then Exit Sub
-
-
With MyRS
-
If .RecordCount > 0 Then
-
'Traverse the Recordset to populate it, if it contains Records
-
.MoveLast
-
End If
-
.FindFirst "[LastName]='" & varLastName & "'"
-
If .NoMatch Then
-
strMsg = "No records found with a Last Name of " & varLastName & _
-
". Do you wish to Add " & varLastName & " to the Database?"
-
intResponse = MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton1, "Add Record")
-
If intResponse = vbYes Then
-
.AddNew
-
![LastName] = Me![txtLastName]
-
![FirstName] = Me![txtFirstName]
-
![EmployeeID] = Me![txtEmployeeID]
-
.Update
-
End If
-
Else
-
MsgBox varLastName & " already exists in Table Employees2."
-
End If
-
End With
-
-
MyRS.Close
-
Set MyRS = Nothing