is there a difference between VBA code for MSaccess & SQL backend?

[SmartCarFun]

Hello All,

I have been using this bit of code to generate a unique reference,

Private Sub SAP_Account_AfterUpdate()
Me.RemitReference.Value = Format([CreatedOn], "yyyymmdd") & "_" & [ID] & "_" & [SAP Account]
End Sub

It worked fine with the MS Access backend but since moving to SQL it won't work? Strangely when creating the number it won't work but on editing the number it works sometimes!!!!

Any help appreciated

 

[mdlueck]

I hardly see how the generation of a custom string would be able to detect what type of back end the database is using.

Do you have any sort of error handling in the code? Perhaps an error is being ignored.

Also, you could consider using a VBA string variable, build the string in the variable, inspect it, and set the field value to the value of the string variable,.

Oh....

AfterUpdate()

I had not noticed that before. So, do you really mean that "After the DB is Updated, THEN compute the value?" And what happens to the computed value After the DB is updated? Would it be too late to matter then? Perhaps there lies your problem.

 

[SmartCarFun]

I thought perhaps because the field [CreatedOn] has a default value of date/time but I can see it being created in SQL

but possible not until it saves the record... if that is the case then it cant find [CreatedOn] & [ID] until the record is saved which would explain it.. is this how SQL works?

I'm not good with string variables never got my head around them....

Thx

 

[mdlueck]

I thought perhaps because the field [CreatedOn] has a default value of date/time but I can see it being created in SQL

The answer to that would be in the table of the SQL BE DB. Check the definition and see if there is a default value for the field.

but possible not until it saves the record... if that is the case then it cant find [CreatedOn] & [ID] until the record is saved which would explain it.. is this how SQL works?

It sounds like you are expecting the SQL BE DB to perform a double update... once to save the data fields and obtain some sort of autonumber ID, then again to tack on your computed value which involves receiving the autonumber ID. Is this what you are trying to do?

Personally I would rather see only one update, and you can always dynamically compute that string involving the autonumber when you look the record back up from the Access FE.

If you need the exact string also stored in the database, then yes, double update would be the only way... and the Access VBA AfterUpdate event would not be the correct tool to use as that is a Form event which fires AfterUpdate... which means Access is not interested in going back to the database again, it thinks it has completed its database work by that time.

I'm not good with string variables never got my head around them....

Then I would suggest a brief detour to brush up your VBA variable skills in that case. Better than... :banghead:

 

[SmartCarFun]

Thanks for the ideas,
Yes I probably need to do some VBA training, I'm just self taught and there are far smarter people out there than me...

I use this code to create a name for a folder. So this code in the original app created a string quickly after the SAP number was entered in a new form as the creating of the new form gave it an [ID] and a [CreatedOn] date. Once the string is created the user can open a folder and drop files into it but at this point they may not have saved the form...

So yes a single update is preferred but I could put a "save" instruction before the string however there are other fields that are "required" so a save might cause a problem if other fields aren't populated...

Am I on the right track?

 

[SmartCarFun]

Update

I could use something like Format(Date(), "yyyymmdd")
But how do I pull the record ID number from SQL without saving it first?

 

[mdlueck]

But how do I pull the record ID number from SQL without saving it first?

If you are using a SQL autonumber capability, YOU DON'T.

 

[rodmc]

I didnt think SQL server did autonumber, isnt it Int data type with some increment code in SQL server?????

 

[mdlueck]

I didnt think SQL server did autonumber, isnt it Int data type with some increment code in SQL server?????

Yes SQL Server has this. It is called IDENTITY

http://msdn.microsoft.com/en-us/library/ms186775.aspx

And it is not dependent on using only one datatype. I have used it with SmallInt, Int, and BigInt cols.