I am a database//Access noob, so bear with me. I have a database set up to keep track of Consultants and their Vendors for a tech consulting company. Some consultants are their own vendors, and some have third-party vendors that handle their contracting. In the case where a consultant is also their own vendor, the contact information is the same for both. Contact Info is stored in a separate table, with primary key ContactID and foreign key fields for ConsultantID (primary key in ConsultantT) and VendorID (primary key in VendorT).
In the case that the relevant Contact Info has already been entered on one of the forms, I want to be able to select the already existing Contact Info record and tell the database to add the other foreign key ID field to the existing record based on the record on the main form. So, for example, if I have already entered Contact Info for Consultant A via the Consultant form, when I open the Vendor form to add Consultant A's vendor I want the option to select "Consultant A" from a combo box and have their info populate VendorsF's Contact Info form while adding the VendorID to the already existing Contact Info record for Consultant A.
I think I've almost worked it out, but am stuck on one last thing. Right now I have a popup form (ChooseConsultantInfoF) for selecting an existing ContactInfo record. On that form I have a search combobox (SelectConsultantCombo) to select the existing record, and a command button (SaveConsultantbtn) which I've tried to code to Update the ContactInfoT and add the VendorID from the current record on the VendorsF to the existing record in ContactInfoT. Here is the all of the code for the popup form:
When I try to put it into action, I get Error 94: Invalid use of Null and it pulls up
I know the code block works apart from that--I tried the Click event once like this:
without any trouble, so the issue must be in calling the combobox's column. Does anyone know why Access doesn't recognize my combobox.column property? Or is there another way to write this up in VBA to avoid this error?
In the case that the relevant Contact Info has already been entered on one of the forms, I want to be able to select the already existing Contact Info record and tell the database to add the other foreign key ID field to the existing record based on the record on the main form. So, for example, if I have already entered Contact Info for Consultant A via the Consultant form, when I open the Vendor form to add Consultant A's vendor I want the option to select "Consultant A" from a combo box and have their info populate VendorsF's Contact Info form while adding the VendorID to the already existing Contact Info record for Consultant A.
I think I've almost worked it out, but am stuck on one last thing. Right now I have a popup form (ChooseConsultantInfoF) for selecting an existing ContactInfo record. On that form I have a search combobox (SelectConsultantCombo) to select the existing record, and a command button (SaveConsultantbtn) which I've tried to code to Update the ContactInfoT and add the VendorID from the current record on the VendorsF to the existing record in ContactInfoT. Here is the all of the code for the popup form:
Code:
Option Compare Database
Option Explicit
Private Sub SaveConsultantbtn_Click()
Dim stupid As Long
stupid = SelectConsultantCombo.Column(0)
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = stupid ;"
End Sub
Private Sub SelectConsultantCombo_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ContactInfoID = " & Me!SelectConsultantCombo
Me.Bookmark = rst.Bookmark
leave:
Me!SelectConsultantCombo = Null
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
End Sub
When I try to put it into action, I get Error 94: Invalid use of Null and it pulls up
Code:
stupid = SelectConsultantCombo.Column(0)
I know the code block works apart from that--I tried the Click event once like this:
Code:
Private Sub SaveConsultantbtn_Click()
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = 1 ;"
End Sub
Last edited: