Add record in VBA with Primary Key autonumber (1 Viewer)

Kayleigh

Member
Local time
Today, 12:46
Joined
Sep 24, 2020
Messages
706
Working on an add record function which moves records from list available and creates new record in related table with item and logID. The trouble is that the primary key of related table is an autonumber so not sure how to add field in DAO. Have looked at some sample code but couldn't find clear-cut answer...
Code so far:
Code:
Private Sub cmdUpdateStudents_Click()

    Dim X As Integer
    Dim i As Variant
    Dim DBS As DAO.Database
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef
        
   Set DBS = CurrentDb
 
    Set td = DBS.TableDefs!tblIncidentLogDetails
    Set rst = td.OpenRecordset
    
  
 
 


    For Each i In Me.lstPossStudents.ItemsSelected
        rst.AddNew
        rst!fldStudentID = Me.lstPossStudents.ItemData(i)
        rst!fldIncidentLogID = Me.fldIncidentLogID
        Debug.Print Me.lstPossStudents.ItemData(i)
        On Error Resume Next
        rst.Update
    
    Next i
    rst.Close
    Set rst = Nothing
    Set td = Nothing
    Me.Refresh
    
    
    Exit Sub



End Sub

Table structure:
tblIncidentLog (fldIncidentLogID as PK) > tblIncidentLogDetails (fldIncidentLogDetailsID as PK; also contains fldIncidentLogID and fldStudentID)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:46
Joined
Feb 28, 2001
Messages
26,996
When an autonumber PK field exists in a table, the DAO to add a new record omits that field even though it fills in everything else that should be filled in. When you declared an autonumber, in essence you handed over control of that field's contents to Access itself. (Well, to the JET or ACE engines, depending on which version of Access you are running...)

Since it is a PK but also an autonumber, that means you never reference it in any SET sub-clause of an UPDATE query or the INSERT INTO's field-list and VALUE list. You CAN reference it in WHERE clauses for a DELETE or UPDATE. In a DAO/VBA sequence, the PK can never be on the left-hand side of any field assignment statement.

ADDENDUM: You didn't find a clear-cut answer because the field can't be referenced by anything that would assign a value.
 

Kayleigh

Member
Local time
Today, 12:46
Joined
Sep 24, 2020
Messages
706
Thanks for clarifying. So I investigated further and found that the trouble with code was that it does not seem to be assigning ID to unbound textbox (this is unbound form) so it could not use ID for adding records.
My workaround was to use the LogID from previous form (should have assigned on open but did not). Don't think its ideal but it does the job.
 

Isaac

Lifelong Learner
Local time
Today, 05:46
Joined
Mar 14, 2017
Messages
8,738
It's very hard to tell from your post what you are trying to do, but I think you're confused about the purpose of keys, primary & foreign.

You've got the right idea, in the sense that, "I need to store the parent table's primary key value somewhere in the child table".
Yes, you do - but not in the child table's PRIMARY key! The child table's primary key should remain an autonumber and never be assigned by you.

You store the parent table's primary key value in a Number (long) column in the child table, and that column is now considered a Foreign Key.
 

Kayleigh

Member
Local time
Today, 12:46
Joined
Sep 24, 2020
Messages
706
I believe I do understand that.
After investigating my forms, I found that the parent form was actually unbound so it did not have an ID to send to the child form. So not having this issue any longer!
Though I'm still not sure the best way to send ID from parent form to child (pop-out) form so it can be referenced in DAO recordset?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:46
Joined
Feb 28, 2001
Messages
26,996
If the parent ID is unbound, I don't know if you can build the automatic link that matches parent to child in the sub-form wizard. If you could, Access would automatically supply it. However, if that doesn't work, then... if the ID exists in the parent form and the child form is in a legit sub-form control, then from the sub-form, you might consider referencing the sub-form's parent. See link for details...


If the ID control in the parent is called PrntID, then from code in the sub-form, you could use Me.Parent.PrntID to get the value of it. NOTE, however, that if the ID in question is bound and the FIELD in the bound table is PrntID, you should name the control something else to avoid confusion. And in that case, use the parent's CONTROL's name rather than the parent's FIELD's name.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:46
Joined
Feb 28, 2001
Messages
26,996
I'm actually a little hazy on that question but I think the answer is NO because a pop-up form doesn't have a parent. It isn't a sub-form, but rather is a new INDEPENDENT form window. I want to stay out of the weeds too deep here, but basically the analogy is that if you launch something using any variant of DoCmd.OpenForm then that form you just opened is NOT a child of the thing that opened it. If you have something that is a form residing in a sub-form control, that IS a child of the form having that control AND you don't launch it with DoCmd.OpenForm. It launches because it is a declared child of the form in which its containing control exists.

Did I answer your question or did I miss your intent?
 

Kayleigh

Member
Local time
Today, 12:46
Joined
Sep 24, 2020
Messages
706
Yes I get that. In my context I am using a pop-out form (unbound) which should be displaying records based on ID of previous form. This usually works although if the first form has opened on a new record it does not seem to have a primary key yet??
This is where I start having issues...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:46
Joined
Feb 28, 2001
Messages
26,996
One possibility is if the thing launching the pop-up CAN cooperate, look into OpenArgs as a means of ID passage. Have the launching form supply the ID in question.


If the launching form CANNOT cooperate, then it gets more complex.

You have a design issue if this pop-up NEEDS an ID but one hasn't been assigned yet. This is a basic "chicken-vs.-egg" conundrum. If the autonumber hasn't been assigned yet, you can't offer it, but then this pop-up has no place to find what it wants. Note that if you cannot commit the new record right away, you will NEVER know the correct ID until it is too late. This is why I've called it a design issue. In essence you are asking a question that cannot be answered at that time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:46
Joined
Feb 19, 2002
Messages
42,970
No one has asked why you are using unbound forms. Access is a Rapid Application Development (RAD) tool. Its primary feature is bound forms. If you are not using the most important feature of your tool, you are using the wrong tool. You will be much happier with some platform other than Access.
 

Users who are viewing this thread

Top Bottom