Home>
Thing I want to do

(1) I want to resolve the error "An attempt was made to assign a NULL value to a variable that is not a Variant type"
(2) In the subform, if you change the records on the second and subsequent lines, I want to avoid changing all the records up to that point in the same way.

In addition, the described VBA refers to the following site.
■Limit the list of combo boxes with input values

Details

I am creating an attendance management form in Access 2007-2016. As shown in the image below, the main form "F_working hours form" and the subform "SF_detailed input form" are displayed in one form, and the table relationship is one-to-many.

〇Detailed input table
・ Details No: Auto number (primary key, hidden on form)
-Client ID: Numerical value (foreign key)
-Task ID: Numerical value (foreign key)
・ Operating time: Numerical value
・ Operating minutes: Numerical value
・ Comment: Text

※reference
〇 T_ client table
-Client ID: Auto number (primary key)
-Client name: Text

Here, in order to enter the client ID of the subform [Detailed input], the following combo box is added to the second name from the left. In event processing, when a client name is selected from "cmb_client name search", the ID of that client is included in the client ID of [Detailed input].

* At first, I thought about using the client ID itself as a combo box, but in the end I could only search by client ID, so I am using this method so that I can search by client name.

〇cmb_ client name search
・ Control source: None
-Value set source: client name, client ID
・ Number of columns: 2
・ Row width: 3 cm, 5 cm

Event content
Private Sub cmb_Client name search_Change ()
    Dim stSQL As String
    stSQL = "SELECT client name FROM T_client table WHERE client name Like'*"&Me.cmb_ client name search .Text&"*';"
    Me.cmb_ client name search. RowSource = stSQL
    If blClick Then
      blClick = False
    Else
      Me.cmb_client name search.Dropdown
    End If
Me.client ID = Me.cmb_client name search.Column (1)
End Sub
Private Sub cmb_Client name search_Click ()
 blClick = True
End Sub
Private Sub cmb_Client name search_KeyDown (KeyCode As Integer, Shift As Integer)
 If KeyCode = vbKeyDown Or KeyCode = vbKeyUp ThenMe.cmb_ client name search. OnChange = ""
    Else
        Me.cmb_client name search.OnChange = "[event procedure]"
    End If
End Sub
Private Sub cmb_Client name search_LostFocus ()
 Me.cmb_client name search.RowSource = _
        "SELECT client name, client ID"&_
        "FROM T_Client Table ORDER BY Client ID;"
End Sub

If i select a client name from "cmb_client name search" here, the error "An attempt was made to assign a NULL value to a variable that is not a Variant type" will occur.

Probably due to the change event "Me. Client ID = Me.cmb_Client Name Search.Column (1)", but when I display "Me.cmb_Client Name Search.Column (1)" in the message box The client ID will come out properly. Why do you get such an error?

Once the error part of ① is eliminated, the client ID shall be entered manually.

If i type in the record on the first line, the same value will be entered on the second line without permission.

If i change the record on the second line

The contents of the first line will change as well. How can I perform "cmb_client name search" line by line?

  • Answer # 1

    Set the properties of the combo box as follows.

    cmb_ client name search

    Control source: Client ID

    Value set source: SELECT client name, client ID FROM T_client table ORDER BY client name;

    Concatenated column: 1

    Number of columns: 2

    Row width: 3cm;5cm

    With this setting, the box will show the client name, but the control source field will showConcatenated column: 1The value of the second column (client ID) specified in is stored.

    This way, if you select a name in the combo box, the ID will be automatically displayed in the client ID text box.

    Once you understand how to use this combo box, you can also turn the client ID text box into a combo box so that you can select by ID or name from the list. See below for details.

    Combo box where you can enter by code or name --hatena chips

    After that, change the event procedure when changing the cmb_client name search as follows.

    Private Sub cmb_Client name search_Change ()
        Dim stSQL As String
        stSQL = "SELECT client name, client ID FROM T_client table"&_
                "WHERE client name Like'*"&Me.cmb_ client name search. Text&"*'"&_
                "ORDER BY client name;"
        Me.cmb_ client name search. RowSource = stSQL
        If blClick Then
          blClick = False
        Else
          Me.cmb_client name search.Dropdown
        End If
    ’Me.Client ID = Me.cmb_Client name search.Column (1) ← Please delete this.
    End Sub

    You should be able to go with this.

  • Answer # 2

    If the control source for cmb_client name search is empty, all lines will have the same value.
    Let's add a search item column to the table to include the control source.