Why is primary key value null?

[beldom]

I'm having the error message "Index or primary key cannot contain null value" pop up even though I haven't updated or otherwise changed the primary key. Why could this be?

[Seth Schrock]

Are you working with related tables at the same time? Do you have any code or macros involved that would be modifying the data?

[beldom]

Nope. The only thing the form does is search (select) items from the table. Tomorrow I will try two things: 1) Delete the table and start a new one to make sure that there are no settings or other criteria that make an empty cell in the primary key field 2) Delete the code in the button and close the form, because that is when the error message appears. Will let you know then if either solution works.

[twinnyfo]

If your primary key is not an autonumber, you may be moving to a new record? If you post the settings of your table and the code in your Form, perhaps we could troubleshoot better.

[beldom]

I don't know what you mean by settings of my table.

Private Sub cmdSearch_Click()
    On Error Resume Next
    Dim ctl As Control
    Dim sSQL As String
    Dim sWhereClause As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Dim strSQL As String
Set db = CurrentDb
    'Initialize the Where Clause variable.
    sWhereClause = " Where "
    'Start the first part of the select statement.
    sSQL = "select * from Patients "
    'sSQL = "INSERT INTO Patients (MedRec,Gender) VALUES (1121, 'R');"
 
    'Loop through each control on the form to get its value.
    For Each ctl In Me.Controls
        With ctl
            'The only Control you are using is the text box. However, you can add as many types of controls as you want.
            Select Case .ControlType
                Case acTextBox
                    .SetFocus
                    'This is the function that actually builds the clause.
                    If sWhereClause = " Where " Then
                        sWhereClause = sWhereClause & BuildCriteria(.Name, dbText, .Text)
                    Else
                        sWhereClause = sWhereClause & " and " & BuildCriteria(.Name, dbText, .Text)
                    End If
            End Select
        End With
    Next ctl
    'Set the forms recordsource equal to the new select statement.
    Me.txtSQL = sSQL & sWhereClause & ";"
    'Me.RecordSource = sSQL & sWhereClause & ";"    'Me.Query = sSQL & sWhereClause   'Me.Requery
    'db.Execute sSQL & sWhereClause & ";", dbFailOnError
 
'Me.List149.RowSource = db.OpenRecordset("select * from Patients;")
Set rs = db.OpenRecordset(sSQL & sWhereClause & ";")
 
    'Do While Not rs.EOF
    '    Me.List149.AddItem (rs![First Name] & ";" & rs![Last Name])
    '    rs.MoveNext
    'Loop
 
                'Dim db As DAO.Database
                'Dim rs As DAO.Recordset
                Dim lst As ListBox
                'strBuild = ""
 
                Set lst = Me![List149]
 
                'Set up the Listbox correctly, if it isn't already
                lst.RowSourceType = "Value List"
                lst.ColumnCount = 3     '##################################################################
                lst.BoundColumn = 3     '##################################################################
                lst.ColumnWidths = "1 in;1 in;1 in" '##################################################################
 
                'Set db = CurrentDb
 
                'Set rs = db.OpenRecordset("SELECT * FROM AnswerT WHERE QuestionID =" & QuestionID)
 
                Do While Not rs.EOF
                    '##################################################################
                    strBuild = strBuild & rs!Gender & ";" & rs![Last Name] & ";" & rs![First Name] & ";"
                     rs.MoveNext
                Loop
 
                lst.RowSource = Left$(strBuild, Len(strBuild) - 1)
 
 
 
 
 
rs.Close
Set rs = Nothing
    Set db = Nothing
    Me.Requery
    Me.Refresh
    End Sub
 
 

[twinnyfo]

Is your primary key an autonumber, or do you set the value in another way?

And, which line of the code you posted is generating the error?

[beldom]

Anyway, I created a new table (called "Patients") and it seems to have fixed the issue.
Anyone know how I can make it so when I input a blank search value the result shows a blank listbox instead of causing Access to freeze and have to be restarted?

[beldom]

It wasn't the code, it was the table. It wasn't an autonumber but it is now, which seems to have been what was wrong...Perhaps when inputting data into the table I forgot to input a primary key (which was the Medical Record # for patients) and even though I later appended the missing field, Access held a grudge. So it would seem.

[twinnyfo]

Concerning Post #7, to prevent errors, when you use a blank control, you can use a condition:

If Not IsNull(Me.ControlName) Then...

That should prevent certain problems...

[beldom]

If Not IsNull(Me.Controls) Then
                    strBuild = strBuild & "" & ";" & "" & ";" & "" & ";"
                     rs.MoveNext
                     End If

This clears the listbox, but also, this kills the database (crashes). What's wrong here?

[twinnyfo]

Insert a break point to determine at which line the DB is crashing. Sounds like something more than building a search string....

[beldom]

Breakpoint doesn't allow code to run, it just highlights the next line when I press F8...What do you mean, 'something more'? I'm just creating an alternative to the expected user input in case they press "Search" by accident when the field is still blank, so the DB doesn't freeze up on them...

[twinnyfo]

What I mean by "something more" is that typically a db does not crash because you build a text string. Sounds like there is something more going on.

And I wanted you to go through the code line by line with F8 to determine at which point the db kills itself. Like just mentioned, it would be terribly strange if the code in post #10 was causing a crash.

[beldom]

If Not IsNull(Me.Gender) Then
    MsgBox "The input field is blank."
 
                Else
    Do While Not rs.EOF
                    rs.MoveFirst
                    strBuild = strBuild & rs!Gender & ";" & rs![Last Name] & ";" & rs![First Name] & ";"
                     rs.MoveNext
                Loop
End If

This is making the msgbox appear despite there being a letter in the input field. What am I doing wrong?

I'm going to implement a combobox for this task (searching patients by gender) in the future but I want to figure out how the Null condition works before I do, for future use.

Please and thanks guys!

[twinnyfo]

Is Me.Gender supposed to be referring to the Text Box or the Field itself? Believe it or not there is quite a difference. If there is no value in the field and the text box is changed, the code may still be looking at the field itself, as the record has not been updated (but is only considered "dirty").

First, some advice: I always intentionally name my text boxes based on what they are "txtGender" in this case or "cboComboBoxName", etc. This makes sure that the VBA will be looking at the specific control you want and not hte record underneath the form (both of which are valid way to refer to things). This also helps, particularly on Reports, when you want to sum across fields, as sometimes, a report will get confused when you want a sum of [FieldName] and there is also a text box named [FieldName].

Second, if, when you want to refer to a field name that has just been updated in a text box, I always guarantee that the field has been updated in the system by forcing a refresh. (Me.Refresh). In your case, if there is a value in the text box named Gender, it will force a save of that value to the field in the table. Then, whether the code is referring to the form's text box or the field itself, these values shoudl be the same.

Some of the experts here may be able to explain this better and understand it better than I can, but this has been my experience, and these few tips have proven very consistent and valuable over time.

[zmbd]

Post# 14
If Me.Gender="A" then

If Not IsNull(Me.Gender) Then 
 

IsNull()= false
then Not() = True
Thus the Message Box at line 2 and not the expect execution at line 5

Please read:
Bytes VBA Insights: What is Null?

[twinnyfo]

Completely missed that "Not" in OP's code....