Full Version: How To Trigger Autonumber Onopen Event Of The Form
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
Tiesto_X
Apr 20 2012, 05:43 AM
Hi,

I have MainForm with 3 Comboboxes. When I fill all 3 CB's and click Add button it opens 2nd form. 2nd form also has 3 Comboboxes with default values based on MainForm's Comboboxes.

OnOpen event I use this code:

CODE
If Me.frmDaysSubform.Form.Recordset.RecordCount = 0 Then
        For i = 0 To 18
        rst.AddNew
        rst("typeWorkID").Value = i + 1
        rst("workerID").Value = Me!cboWorker.Column(0)
        rst("rListID").Value = Me!rListID
        rst.Update
        Next i
        Set rst = Nothing
     End If


The problem is because Master and Child field rListID has no values since form is not Dirty.

Is there chance to trigger autonumber for rListID field OnOpen even on the form?

Thanks in advance.
arnelgp
Apr 20 2012, 06:01 AM
What to you mean "open" second form? Is it subform or a pop up form?
Tiesto_X
Apr 20 2012, 06:03 AM
My bad.

Second form is Popup form with subform.
arnelgp
Apr 20 2012, 06:17 AM
Will a Requery on the subform recordset from the the form's Load Event (pop up main form) do any good:

CODE
Private Sub Form_Load()
    Me.frmDaysSubform.Form.Requery
End Sub
BruceM
Apr 20 2012, 06:18 AM
The Open event is too soon to work with the recordset, as it has not yet been loaded.

I don't see that you have defined rst. Presumably it is a recordset, but what recordset?

QUOTE
rst("workerID").Value = Me!cboWorker.Column(0)

If this is in an event on the form you are opening, you need a different syntax to refer to the calling form (the form from which you opened this form) to refer to cboWorker. As the code is written it references a control on the form being opened, not the calling form. To reference that form, something like:
Forms!FormName!cboWorker

Why are you adding 18 records? Is it 18 records every time?

QUOTE
Is there chance to trigger autonumber for rListID field OnOpen event on the form?

What do you mean by "trigger" the autonumber. Autonumbers are added automatically to new records.
BananaRepublic
Apr 20 2012, 06:20 AM
A question -- is the first form actually bound?

I think we need more explanation on exactly how the first form and second form relate.
arnelgp
Apr 20 2012, 06:25 AM
CODE
The Open event is too soon to work with the recordset, as it has not yet been loaded.


It is already loaded since it is a subform.
BruceM
Apr 20 2012, 09:14 AM
QUOTE
It is already loaded since it is a subform.

Yes, the subform loads before the main form (whose idea was that anyhow?), but the OP seems to be attempting to work with the main form's recordset. Assuming rst is a recordset, and the subform is its source, it seems the OP wants to create 18 subform records that are identical except that TypeWorkID increments by 1 with each record. Leaving aside that this raises questions about the design, as the code is written Me!cboWorker.Column(0) (for instance) is a field on the main form, and that recordset is not yet available in the Open event.
If instead the idea is to reference cboWorker.Column(0) on the calling form, the syntax needs to be changed as I noted previously.




fkegley
Apr 20 2012, 03:58 PM
QUOTE
Yes, the subform loads before the main form (whose idea was that anyhow?),


BruceM, our usage of the term subform--yes, I do it, too--is something of a misnomer.

Technically, there is no such thing as a subform, there are only forms.

However, there is a subform control, which can contain a form that displays a recordset, that can be placed on another form.

fkegley
Apr 20 2012, 04:01 PM
One thing you could do is use your own autonumber function.

In a table, store the beginning value.

Then as part of your existing code, fetch the existing value from the autonumber table, assign it to a (say) control, add 1 to it and update the value in the AutoNumber table.
Tiesto_X
Apr 21 2012, 12:37 PM
Hi guys,

thanks everyone for your response!

Problem is solved!

Bruce, you was right, I dont need this:

CODE
rst("workerID").Value = Me!cboWorker.Column(0)


The reason why I need 18 records all the time in subform take a look of pictures. Its like some kid of excel looking form.

fkegley, you was right, I needed my own autonumber like unbound texbox, and other fields also unbound but subform is bound.

Unbound texboxes takes values from Main form and OnOpen event they get inserted into table with INSERT INTO command. For autonumber I just used Dmax+1.

Here's a whole code, but field names are not on English.

CODE
Private Sub Form_Open(Cancel As Integer)
Dim rnSQL As String
Dim intZaposleni, intMesec, intGodina, intOrgJed As Integer
Dim intrListID As Integer

Me!rListID.Value = DMax("rListID", "tblRadneListe") + 1
intrListID = Me!rListID.Value

Me!cboZaposleni = Forms!frmRadneListeMeni!txtZaposleni.Value
Me!cboMesec = Forms!frmRadneListeMeni!txtMesec.Value
Me!cboGodina = Forms!frmRadneListeMeni!cboGodina.Value

intZaposleni = Me!cboZaposleni.Column(0)
intMesec = Me!cboMesec.Column(0)
intGodina = Me!cboGodina.Column(0)
intOrgJed = Me!cboOrgJed.Column(0)

rnSQL = "INSERT INTO tblRadneListe (ZaposleniID,MesecID,GodinaID,OrgJedID,rListID) SELECT " & intZaposleni & ", " & _
"" & intMesec & ", " & intGodina & ", " & intOrgJed & "," & intrListID & ""
DoCmd.RunSQL rnSQL

SubFormUpdate
End Sub


CODE
Private Function SubFormUpdate()
Dim rst As DAO.Recordset
Dim i As Long
Dim updSQL As String
Dim lkup As Integer
Dim rnSQL As String

Set rst = Me!frmDaniSubform.Form.Recordset

    If Me!frmDaniSubform.Form.Recordset.RecordCount = 0 Then
        For i = 0 To 18
        rst.AddNew
        rst("vrstaRadaID").Value = i + 1
        rst("rListID").Value = Me!rListID
        rst.Update
        Next i
        Set rst = Nothing
    End If

Me!frmDaniSubform.Form.Enabled = True
Me!frmDaniSubform.Form.AllowAdditions = False

End Function


But if I want make changes I just made same form just with Bound fields.

Thanks everyone for help!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.