Moving auto Number to a field on a different table?

[d9pierce1]

Hi all,


Good morning

Below is code for what I am working on and ran into a glitch. I need to find a way once the record is created in strSql, to then get the auto number from that and put it in a different table. See bleow in RED

Private Sub CboInstitutionSearch_NotInList(NewData As String, Response As Integer)
    Dim strsql As String
    Dim maxID As Long
    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


    
    "After above puts the [InstitutionName] into that table I need to get the [InstitutionID] AutoNumber from that record I just created,
    and put that [InstitutionID] in column InstitutionID into the table "tblOnLineAccess" as a new record"
    Not sure if an Insert Into or Update or what is needed here to accomplish this?


    Response = acDataErrAdded
    Me.CboInstitutionSearch = Null
    Me.Requery
    maxID = DMax("InstitutionID", "tblInstitution")
    Me.RecordsetClone.FindFirst "InstitutionID=" & maxID
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Thank you all,
Dave

[Welshgasman]

See if this helps.
https://www.accessforums.net/showthr...light=Identity

[d9pierce1]

Thanks Welshgasman,
When Valad wrote this code for me, it was to take an autonumber and add it to a form. I have no idea how I would take the autonumber from one table and put it into a field in another table using that code.
I am sure it is possible but way over my knowledge to alter that. Not real sure?

[Welshgasman]

Well surely it would just be obtaining that value then inserting into the other table?, if record exists, then it would be an update?

[d9pierce1]

are you saying something like?

sSql = "UPDATE tblOnLineAccess Set InstitutionID = InstitutionID Where tblInsitiution.InstitutionName = " & CboInstitutionSearch

Sure I dont have this correct with syntex and such but is this what you would be suggesting?
Thanks
Dave

[Welshgasman]

Well you cannot set an autonumber, so that must be a normal long field, but if institutionid is the value of the @@identity retrieved, then yes if that search name exists, else it to would be an insert.

[June7]

Actually, it is possible to set an autonumber field with an SQL INSERT action, but that is for another discussion.

If this is a single user db, DMax() should serve but if there are multiple simultaneous users creating records, it will not work. Could use @@IDENTITY.

CurrentDb.Execute "INSERT INTO tblOnLineAccess(InstitutionID) VALUES(" & CurrentDb.OpenRecordset("SELECT @@IDENTITY").FIELDS(0) & ")"

All this complication would go away if you used bound form/subform arrangement.

[d9pierce1]

PasswordVault (1) - Copy.zip

Hi June
That was just what I wanted. I do have one question as when I add a record with that, it works great except it adds the InstitutionID to the table where it suppose to go and then adds it to the website too. I attached a bogus copy for clarification. Very small db
Not sure why it adds it twice to two different fields?

Thanks

[June7]

That is odd.

I just tested your code with step debug and it did not put value in Website. But then I clicked on form and something populated that field.


Again, using DMax() with multiuser db is likely to fail.

Set a variable to the @@IDENTITY value and use variable in the SQL and FindFirst code.

[d9pierce1]

Thank you June,
I will try to figure that out
Dave

[June7]

I UNBOUND Website textbox. It still gets a value but record in table does not.

Deleting control prevents form opening.

I always name controls different from field or objects, like tbxWebsite. Then qualify references in code: Me.tbxWebsite, Me!Website. You have reference to Website without qualifying. These adjustments might help track down issue.

Private Sub cmdWebsite_Click()
With Me
    If Left(.tbxWebsite, 7) = "http://" Or Left(.tbxWebsite, 8) = "https://" Then
        Application.FollowHyperlink .tbxWebsite
    Else
        Application.FollowHyperlink "http://" & .tbxWebsite
    End If
End With
End Sub


Private Sub tbxWebsite_AfterUpdate()
With Me
    If Left(.tbxWebsite, 7) = "http://" Then .tbxWebsite = Right(.tbxWebsite, Len(.tbxWebsite) - 7)
    If Left(.tbxWebsite, 8) = "https://" Then .tbxWebsite = Right(.tbxWebsite, Len(.tbxWebsite) - 8)
End With
End Sub

Textbox name change makes no difference in behavior. Really mystifying why deleting textbox prevents form opening. I am totally stumped by this issue.


Suggest removing DefaultValue of 0 from UserId field in tblOnLineUserAccess.


Current design allows creation of log record without details.

[d9pierce1]

Thanks for all the help with this. I will continue to play with it and in the mean time, its not really an issue to get rid of the extra as I put a web address in that any way.
I have absolutely no idea either and will try different things.
Again,
Thanks

[June7]

I tried deleting Website textbox again and this time don't get error when form opens - form works. I created a new textbox and the issue went away. I believe I tried this before renaming textbox and it failed. So maybe renaming, deleting/recreating, compact & repair did correct something.

[d9pierce1]

New Test Copy.zip
Hi all,
I Made the changes recomended by June and it works well except for this anoying error about validation.
I dont have any validation in this entire db? I uploaded above a test copy of this db with changes June suggested
Error Below
Other than that, it works just as expected. I cant find where this error is coming from.
Thank you,
Dave

[June7]

How is that message triggered? What actions are you doing when it happens? I am not getting it.