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?