Combobox.column property won't allow selection of column(0) in VBA (1 Viewer)

AliG

Registered User.
Local time
Yesterday, 21:28
Joined
May 3, 2016
Messages
21
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:

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
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?
 
Last edited:

sxschech

Registered User.
Local time
Yesterday, 18:28
Joined
Mar 2, 2010
Messages
791
Did you try:

Code:
stupid = Forms!VendorsF.SelectConsultantCombo.Column(0)
 

moke123

AWF VIP
Local time
Yesterday, 21:28
Joined
Jan 11, 2013
Messages
3,852
stupid = me.SelectConsultantCombo.Column(0)?
 

JHB

Have been here a while
Local time
Today, 02:28
Joined
Jun 17, 2012
Messages
7,732
You need to get the value of stupid into the SQL-string.
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = " & stupid
 

AliG

Registered User.
Local time
Yesterday, 21:28
Joined
May 3, 2016
Messages
21
I tried stupid = Me.SelectConsultantCombo.Column(0) as well as
stupid = Forms!VendorsF.SelectConsultantCombo.Column(0) and still get "invalid use of null".

@JHB, I couldn't get your suggestion to work either. I tried a few ways:
Code:
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = " & stupid
Code:
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = " & stupid ;"
Code:
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = " & stupid ;
All return "Compile error: Expected: End of statement"

When I try:
Code:
DoCmd.RunSQL "UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = " & stupid & ";"
I get no errors with the SQL statement but the debugger still pulls up
Code:
stupid = SelectConsultantCombo.Column(0)
with an Invalid use of null error.

On a whim I thought to try
Code:
stupid = SelectConsultantCombo.Value
as the column I was trying to call is also the bound column, but that also returns Invalid use of Null.
Am I missing something?:confused:

EDIT: I'm not sure if this may be useful, but here is the Row Source for the combobox-
Code:
SELECT ContactInfoT.ContactInfoID, ConsultantT.ConsultantID, ConsultantT.FirstName, ConsultantT.LastName FROM ConsultantT 
INNER JOIN ContactInfoT ON ConsultantT.ConsultantID = ContactInfoT.ConsultantID 
ORDER BY ConsultantT.[LastName], ConsultantT.[FirstName];
 
Last edited:

Cronk

Registered User.
Local time
Today, 12:28
Joined
Jul 4, 2013
Messages
2,770
Stupid is declared a long variable. You will an error message if you assign a string or null to it. Similarly with the Sql string.

Check the value of the combo with
Debug.print me.select consultant combo
At the start of your procedure.
 

moke123

AWF VIP
Local time
Yesterday, 21:28
Joined
Jan 11, 2013
Messages
3,852
have you tried taking these lines out...
Code:
leave:
Me!SelectConsultantCombo = Null
 

JHB

Have been here a while
Local time
Today, 02:28
Joined
Jun 17, 2012
Messages
7,732
Post your database with some sample data.
 

GinaWhipp

AWF VIP
Local time
Yesterday, 21:28
Joined
Jun 21, 2011
Messages
5,901
Try...

Code:
"UPDATE ContactInfoT SET VendorID = (Forms!VendorsF!VendorID) Where ContactInfoID = " & Nz(Me.SelectConsultantCombo,0)
 

AliG

Registered User.
Local time
Yesterday, 21:28
Joined
May 3, 2016
Messages
21
@moke123: I took
Code:
Me!SelectConsultantCombo = Null
out and it works perfectly now! Of course if I have the combo set to null somewhere in the code it's going to pull up null somewhere else, d'oh!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:28
Joined
Sep 12, 2006
Messages
15,614
note also that the columns in a combo box are strings.

In most some cases access will seamlessly convert a string to a number ... but sometimes you have to do this yourself, or you get a type mismatch

I don't think that is the issue here, though.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:28
Joined
Feb 28, 2001
Messages
27,001
In combo boxes, I usually use something like

Code:
if me.cboBox.ListIndex <> -1 then
    stupid = me.cboBox(0, me.cboBox.ListIndex)
else
   msgbox "No selection available"
end if

Setting the combo box to Null elsewhere in your code would have had the effect of deselection of any row in cboBox, thus resetting .ListIndex to -1, which this little code sequence would have caught for you.
 

sneuberg

AWF VIP
Local time
Yesterday, 18:28
Joined
Oct 17, 2014
Messages
3,506
In combo boxes, I usually use something like

Code:
if me.cboBox.ListIndex <> -1 then
    stupid = me.cboBox(0, me.cboBox.ListIndex)
else
   msgbox "No selection available"
end if

What's stupid = me.cboBox(0, me.cboBox.ListIndex) supposed to be doing? I don't think a combo box has parameters like that.
 

JHB

Have been here a while
Local time
Today, 02:28
Joined
Jun 17, 2012
Messages
7,732
Sorry, I haven't seen the "Column" was missing, so you're right.
 

Users who are viewing this thread

Top Bottom