Get ID of created record overflow problem (1 Viewer)

George-Bowyer · Mar 6, 2019

After lots of googling, I found an answer that says that if (and only if) you are using an access BE as well as FE, you can get the ID of a newly created record by filling a variable before .update.

So I tried this code:

Code:

                            .AddNew
                        
                            !fldOrgPosOrg = 308
                            !fldOrgPosPerson = intPerson
                            !fldOrgPosPosition = 54
                            If Not IsNull(Me.txtStartDate) Then !fldStartDate = datDate
                            !fldDateChanged = Date
                            !fldChangedBy = Form_NavigationForm.cmbUser
                            intNewID = !OrgPosID
                            .Update

But unfortunately the "intNewID = !OrgPosID" line causes an overflow error.

I need the ID so that I can immediately create a linked record in another table.

Does anyone have any suggestions, please?

Many thanks.

George

Minty · Mar 6, 2019

I suspect you have used

Code:

Dim intNewID as Integer

and the value being returned is > 32,767

Use

Code:

 Dim intNewID as Long

To allow much bigger values to be used.

CJ_London · Mar 6, 2019

pretty sure the OrgPosID will not be properly populated until after the .Update

have you tried

debug.print !OrgPosID

before assigning to intNewID?

Also suspect ADO behaves differently to DAO - so if still a problem, you need to clarify which type you are using

George-Bowyer · Mar 6, 2019

Minty said:
I suspect you have used

Code:

Dim intNewID as Integer

and the value being returned is > 32,767

Use

Code:

Dim intNewID as Long

To allow much bigger values to be used.

Yep. That fixed it. Thanks.

George-Bowyer · Mar 6, 2019

CJ_London said:
pretty sure the OrgPosID will not be properly populated until after the .Update

Well, now that I'm using a long variable rather than an integer, it seems to work ok.

The article I filched it from did say that it only worked with an Access BE and not with other types.

Also suspect ADO behaves differently to DAO - so if still a problem, you need to clarify which type you are using

Eep!

Get ID of created record overflow problem (1 Viewer)

George-Bowyer

Registered User.

Minty

AWF VIP

CJ_London

Super Moderator

George-Bowyer

Registered User.

George-Bowyer

Registered User.

Similar threads

Users who are viewing this thread