Open Form after Insert SQL Command

[Post=73083 Scaper]

Thanks to Hans, I have been able to piece together the following code:

Code: Select all

Private Sub cmdAdd_Click()
    Dim lngGraduateID As Long
    Dim sql As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    On Error GoTo sbGAP_Error
    stDocName = "frmGAP"
    sql = "INSERT INTO tblGAP (GraduateID) Values (" & GraduateID & ")"
   
    CurrentDb.Execute sql
    stLinkCriteria = "[GraduateID]=" & Me![GraduateID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub

sbGAP_Error:
    If Err.Number = 3022 Then
    Resume Next
    Else

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Add Graduate "
    End If
End Sub


However, when I click the button, at first it opens the correct Form, but the Graduate field is blank. But if I click the button again, it opens the Form, and this time it has populated the 'Graduate' field with the FK that was inserted via the SQL Command.

I tried to do a Refresh and/or Requery to force Access to update the Table, but it produced a error. I then tried to close the Form, hoping it would force the Refresh, but then it failed to open the form because it couldn't match the ID Fields.

Is there a way to get Access to Insert this new Record into the Table and then open the Form based on this Table with the new Data populated?

Many thanks in advance for any help.

Brian

[HansV]

Does it work better if you change

Code: Select all

    CurrentDb.Execute sql


to

Code: Select all

    DoCmd.SetWarnings False
    DoCmd.RunSQL sql
    DoCmd.SetWarnings True

[Post=73089 Scaper]

Hi Hans
Thanks for the reply. I tried that, but it resulted in the same thing: the form opened, but the 'Graduate' field was not populated. :(
Is there something else I could try?
Many thanks again
Brian

[HansV]

What if you insert a line

DoEvents

above the line that opens the form?

[Post=73091 Scaper]

Unfortunately not. :(

[HansV]

Could you create a stripped-down copy of the database without sensitive or proprietary information, zip it and attach the zip file to a reply? (Max. file size 250 KB)

[Post=73102 Scaper]

Sure. Thanks Hans.
The Form with the Code is the one that opens when the DB opens. At the bottom is the button that users will press. When they Click it, the new person they've added should be added to the Table, and then the Form (on which the Table the person's name was added to) should open with that new Entry in the Graduate field.

Hope this makes sense.

Thanks, as always

Brian

[HansV]

The problem is that the record in frmGraduateNEW hasn't been saved yet, so there is no record for it in tblGraduate yet.
The solution is to save the record before executing the insert SQL:

Code: Select all

Private Sub cmdAdd_Click()
    Dim sql As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    On Error GoTo sbCreateReviews_Error
    ' ***** Save the record if necessary *****
    If Me.Dirty Then Me.Dirty = False
    sql = "INSERT INTO tblGAP (GraduateID) Values (" & GraduateID & ")"
    CurrentDb.Execute sql
    stDocName = "frmGAP"
    stLinkCriteria = "[GraduateID]=" & Me![GraduateID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub

sbCreateReviews_Error:
    If Err.Number = 3022 Then
        Resume Next
    Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Add Graduate "
    End If
End Sub


Note that I went back to CurrentDb.Execute - that is more efficient than DoCmd.RunSQL.

[Post=73107 Scaper]

Awesome. Thanks Hans. Funny, I was just wondering that myself, but I thought it would be a VB Save Command or something. I've always wanted to understand VBA better than I do. What does
If Me.Dirty Then Me.Dirty = False mean? Obviously, it seems that it forces Access to Save the Record, but why not use DoCmd.Save?
Dirty must mean 'A Save has not occurred', but why does Me.Dirty = False result in a Forced Save? It seems an Odd way to do something.

I guess I just don't understand VBA well enough or don't think like a programmer, but it just doesn't seem intuitive. :(

Anyway, thanks as always for sorting my conundrum.

Have a great night and day tomorrow.

Brian

[HansV]

DoCmd.Save does not save the record, but the design of the form.

The Dirty property of a form is True if changes to the current record haven't been saved. I agree that it is somewhat counterintuitive, but setting Dirty to False actually saves the record. Another way of saving the record is

RunCommand acCmdSaveRecord

[Post=73125 Scaper]

Thanks for explaining it Hans. Makes much more sense now.

Really appreciate all your help, as usual.

Take care

Brian

[Post=80149 Scaper]

Hi Hans
Hope all is well. :)

I had to make a couple of design changes to this database, and when I did a Compact & Repair after they were all implemented, this code no longer works. I've gone back into the VB (-> Debug -> Compile), but the Code still isn't working properly: the new Record isn't inserted into the Form, but the form *does* open--it's just blank. So, it appears the new record isn't being inserted through the SQL Statement.
I am getting an error when compiling:

Code: Select all

Me.ReferralDate.Locked = False

Any thoughts? :(

Many thanks in advance

Brian

[HansV]

Could you post a stripped down and zipped copy of the database again?

[Post=80165 Scaper]

Thanks Hans
I'll upload the Backup in a few moments . . .
The Code you supplied is behind the only button on the Form: Add a Graduate

[Post=80166 Scaper]

Here's the Back-end.

Many thanks, as always, for your help! :)

[HansV]

I don't get a compile error, but the execution of the SQL statement

sql = "INSERT INTO tblGAP (GraduateID) Values (" & GraduateID & ")"

fails because you don't set a value for GAPstaff_FK.

[Post=80171 Scaper]

Is that because I made some Fields in the DB Mandatory, and Access now won't allow a new Record to be inserted because the Mandatory fields aren't being filled in?

If so, do I need to add those Fields to the Graduate Form so they are available to be inserted at the time of the ...

Code: Select all

sql = "INSERT INTO tblGAP (GraduateID) Values (" & GraduateID & ")"

[HansV]

Yes, you need to provide a value for GAPstaff_FK, LC, and ReferralDate. They are required fields, so you can't leave them blank.

[Post=80216 Scaper]

Thanks Hans. Should have realized that. Sorry for being so stupid. :(

Just to clarify, will the SQL line look like this

Code: Select all

sql = "INSERT INTO tblGAP (GraduateID, GAPstaff_FK, LC, ReferralDate) Values (" & GraduateID, GAPstaff_FK, LC, ReferralDate  & ")"

Or

Code: Select all

sql = "INSERT INTO tblGAP (GraduateID, GAPstaff_FK, LC, ReferralDate) Values (" & GraduateID & GAPstaff_FK & LC & ReferralDate & ")"

[HansV]

The values must come from your form, but the commas separating them must be part of the SQL string. A date value must be enclosed in # characters, and it must be provided either in US date format mm/dd/yyyy or in the unambiguous yyyy/mm/dd format. So it'd look like this:

Code: Select all

    sql = "INSERT INTO tblGAP (GraduateID, GAPstaff_FK, LC, ReferralDate) Values (" & GraduateID & _
        ", " & GAPstaff_FK & ", " & LC & ", #" & Format(ReferralDate, "yyyy/mm/dd")  & "#)"