Adding an ID in Website on Form?

[d9pierce1]

Password.zip

Hi all,
DB attached.
On Form (frmOnLineAccess) when I enter a new name into field Search Institutions, it fire a Not in List, which it is suposed to, and When save it put an ID into the Website Field?
Has something to do with the code below in RED but not sure why it is putting it in both the InstitutionID and the Website? Should only go in the InstitutionID in the corresponding table.

Baffles me, maybe someone will see what the problem is on this. I think Valid helped me with this a long time ago and I never have been able to fix it?

Private Sub CboInstitutionSearch_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    Dim maxID As Long
    On Error Resume Next
    
    If MsgBox("Institution is not in Current List, Would you Like to Add?", vbYesNo) = vbNo Then
        Me.Undo
        Me.CboInstitutionSearch = Null
        Response = acDataErrContinue
        Exit Sub
    End If
    strSQL = "Insert Into tblInstitution ([InstitutionName]) values ('" & NewData & "')"
    CurrentDb.Execute strSQL, dbFailOnError
    CurrentDb.Execute "INSERT INTO tblOnLineAccess(InstitutionID) VALUES(" & CurrentDb.OpenRecordset("SELECT @@IDENTITY").Fields(0) & ")"
    Response = acDataErrAdded
    Me.CboInstitutionSearch = Null
    Me.Requery
    Me.TxtWebsite.SetFocus
    maxID = DMax("InstitutionID", "tblInstitution")
    Me.RecordsetClone.FindFirst "Institution=" & maxi
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Thank you


Dave

[CJ_London]

not verified the legitimacy of your code but you need to use a db object, not currentdb. Currentdb refreshes the instance every time it is referenced

dim db as dao.database
set db=currentdb
strSQL = "Insert Into tblInstitution ([InstitutionName]) values ('" & NewData & "')"
Db.Execute strSQL, dbFailOnError
Db.Execute "INSERT INTO tblOnLineAccess(InstitutionID) VALUES(" & Db.OpenRecordset("SELECT @@IDENTITY").Fields(0) & ")"

[d9pierce1]

Thanks,
I changed this and it still puts the InstutionID into the Website?

[isladogs]

Agree with using a variable db but that won't affect whether your code runs without error.

I tested the code in A365 version 2510 Beta Channel 64-bit and it worked perfectly for me

Attachment 53257

[d9pierce1]

Thank you,
I can actually put in the website and that is OK , Like You Did, but why when you add a new (Not in List) and hit save button does it put the ID in the Website If you dont enter a website?

[isladogs]

Ah I see - you omitted the most important point in your explanation

This works for me:

CurrentDb.Execute "INSERT INTO tblOnLineAccess(InstitutionID, Website)" & _
        " VALUES(" & CurrentDb.OpenRecordset("SELECT @@IDENTITY").Fields(0) & ", '" & Nz(Me.TxtWebsite.Value, "") & "')"

EDIT:
Further testing showed there is a different problem. I'll try to look at it again later.

[d9pierce1]

Ah I see - you omitted the most important point in your explanation

This works for me:

CurrentDb.Execute "INSERT INTO tblOnLineAccess(InstitutionID, Website)" & _
        " VALUES(" & CurrentDb.OpenRecordset("SELECT @@IDENTITY").Fields(0) & ", '" & Nz(Me.TxtWebsite.Value, "") & "')"

EDIT:
Further testing showed there is a different problem. I'll try to look at it again later.

Thanks, Using the origional code I changed the "Me.TxtWebsite.SetFocus" to "Me.InstitutionName.SetFocus" and that got rid of the ID in the Website Field.
Not sure why but it did work?
Thanks again,
Dave

[isladogs]

Glad you found a workaround.
Doing that also prevents you clicking Save unless the Website field is populated which solves another problem!

I suspect the code could be significantly simplified but as you have a solution, I'll leave it alone!

[jojowhite]

you may also try this:

[d9pierce1]

you may also try this:

Thanks JoJowhite
That works just beautifully
Dave