Get ID of created record overflow problem

[George-Bowyer]

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:

                            .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]

I suspect you have used

Dim intNewID as Integer

and the value being returned is > 32,767

Use

 Dim intNewID as Long

To allow much bigger values to be used.

 

[CJ_London]

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]

I suspect you have used

Dim intNewID as Integer

and the value being returned is > 32,767

Use

 Dim intNewID as Long

To allow much bigger values to be used.

Yep. That fixed it. Thanks.

 

[George-Bowyer]

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!