Coding an "update" button

[Nick F]

I have a form (based on Event_Partners table) that I type in First Name, Last Name, Organization, etc for partners sponsoring an event. I have a separate table that contains partner_contact_information (First Name, Last Name, phone, cell, fax, email, web, etc.). I want to have a button on my Events_Partners form named "Update Contact Info". When clicked, this button would search, based on first AND last name, for a record in the partner_contact_information table and pull up that record. If there is no record, a new record would be started. I made queries to combine first and last name in both tables. I am a VBA novice and need help writing the code for the button. Thank you.

[June7]

You want the button on Events_Partners form to open another form with the contact information?

Do you have a combobox on the Events_Partners form for selecting partner? Look into the NotInList event of the combobox. This event would open the ContactInfo form to new record if partner not found in the RowSource.

Then the button would only need to open contact edit form to an existing record using the value in combobox as criteria:
DoCmd.OpenForm "ContactInfo", , ,"PartnerID=" & Me.cboPartner

This way the search criteria is a unique ID and even if there is more than one John Smith, only one record can be retrieved.

[Nick F]

Thanks June. I hadn't thought about it that way. That leads to another question. Say you have a John Smith and you type in another John Smith, how do you get the NotInList event to fire?

[June7]

Good question. Where I have used the event the data entered is unique ID (a lab sample number or project number or test number). My databases don't deal with people and their names (project names, yes, and that does give me some headache).

You will have to know the AccountNumber or whatever combination of data to determine unique identity (address, phone, account, SSN). You can have the ID display in the combobox list. If ID not there then maybe type Smith in a form not in the list (Smyth, Smythe, Smithe, whatever) to trigger the partner contact info form and fix the name there.

The issue is the same even if you don't use the NotInList event and code completely behind button. How to know this is a new John Smith? Only unique identifying info can help determine that.

[Nick F]

I gave this some more thought and would like your advice/comments/help. What if I put a text box on my Event_Partners form that combines the first and last name (=[Event_Partners_subform].[Form]![Partner_Name_First] & [Event_Partners_subform].[Form]![Partner_Name_Last]). I do the same for the Contact_Partner_Info form. If I get it working, I will just make these text boxes invisible. Then I built a button and wrote some code.

Private Sub Command95_Click()
Dim FullName As String
FullName = Forms![Event_Partners]!Partner_Full_Name
DoCmd.OpenForm "Contact_Partner_Info"
DoCmd.SearchForRecord acActiveDataObject, , acFirst, "[Contact_Full_Name] = '" & FullName & "'"
End Sub

It opens up the Contact_Partner_Info form but does not do the SearchForRecord. It just goes to the first record. Ideas?
Side note: I am not worried if there is more than one person for one name such as John Smith. I don't expect to run into this issue very often. If I do, the user can just scroll through the few records to find the right one. If it is a new name, they can just click add record button on the Contact_Partner_Info form to add the contact.

[June7]

I have never used SearchForRecord. Maybe not working because the calling form is the active object.

I open form with filtered recordset. Don't really need the invisible textbox and variable. You said the button will be on the subform and the subform has the name info? Assuming there is a field called [Contact_Full_Name] in the called form that concatenates name parts same way:

DoCmd.OpenForm "Contact_Partner_Info", , , "[Contact_Full_Name] = '" & Me.[Partner_Name_First] & Me.[Partner_Name_Last] & "'"

[Nick F]

June, thank you. I was making it more complicated than I needed. I got everything to work how I wanted. Here is how I coded the button.

Private Sub Update Contact Infro_Click()
Dim FirstName As String
Dim LastName As String
Dim Org As String
FirstName = Me.Partner_Name_First
LastName = Me.Partner_Name_Last
Org = Me.Partner_Organization
DoCmd.OpenForm "Contact_Partner_Info", , , "[Contact_Full_Name] = '" & Me.[Partner_Name_First] & Me.[Partner_Name_Last] & "'"
If IsNull(Forms!Contact_Partner_Info.[Name_First]) Then
Forms!Contact_Partner_Info.[Name_First] = FirstName
Forms!Contact_Partner_Info.[Name_Last] = LastName
Forms!Contact_Partner_Info.[Contact_Organization] = Org
Forms!Contact_Partner_Info.[Contact _Phone_Land].SetFocus
Else
Forms!Contact_Partner_Info.[Contact _Phone_Land].SetFocus
End If


End Sub

Thanks again!