Save Record in VBA

[RAG2007]

ADP front end, SQL Server backend.

On my main form, I need to pull up in a select query in values from a record I am in the process of adding, before SQL Server actually saves the record to give me the Primary Key ID, which is autoincrement. I've tried docmd.runcomman d accmdsaverecord , but it is giving me this:

Run-time error '7874':

Access can't find the object 'SELECT ((0)) AS Column1, ((0)) AS Column2,((0)) AS Column3,((0)) AS Column4,((0)) AS Column5,((0)) AS Column6,((0)) AS Column7.'

I'm not sure where this select statement is coming from, but I get this error in multiple places where I'm trying to save the record in VBA.

What's going on? Is there another way to save a record in VBA other than this?

Help! I'm running out of steam and have a deadline.

[Minion]

I'm not entirely sure what you are trying to do here. Are you using a form to enter the data inot the record, and if so why not just call the values from the form rather than the record itself?

Pleae elaberate on the set up of the project and what it is you're trying to accomplish so that we might better help you.

- Minion -

ADP front end, SQL Server backend.

On my main form, I need to pull up in a select query in values from a record I am in the process of adding, before SQL Server actually saves the record to give me the Primary Key ID, which is autoincrement. I've tried docmd.runcomman d accmdsaverecord , but it is giving me this:

Run-time error '7874':

Access can't find the object 'SELECT ((0)) AS Column1, ((0)) AS Column2,((0)) AS Column3,((0)) AS Column4,((0)) AS Column5,((0)) AS Column6,((0)) AS Column7.'

I'm not sure where this select statement is coming from, but I get this error in multiple places where I'm trying to save the record in VBA.

What's going on? Is there another way to save a record in VBA other than this?

Help! I'm running out of steam and have a deadline.

[Jim Doherty]

I'm not entirely sure what you are trying to do here. Are you using a form to enter the data inot the record, and if so why not just call the values from the form rather than the record itself?

Pleae elaberate on the set up of the project and what it is you're trying to accomplish so that we might better help you.

- Minion -

Hi RAG2007,

Neither am I! Rag if you don't know where that SQL is coming from there is not much chance of others knowing either and contributing something meaningful unless there is more detail regarding the potential source code causing it?

(Minion I love your signature by the way hahaha)

Jim

[RAG2007]

I'm creating a Project ID number based on the information of the currently entered project. That ID is based off of a code corresponding to the client company we work with. I have to pull up related that code from a separate table not in the recordsource. I was able to do this when Access was the backend, because access saves the record as soon as info is entered; SQL Server doesn't seem to do that, so I need a way to save the record to then use that record's info to pull up other data. When I used accmdSaveRecord , I got the error, including that really wierd select statement which I have no idea where it comes from.

Does that clarify? I could add more code to illustrate...

I actually rebuilt the entire form and reworked the way I was doing it, so the workaround is OK. But I'm worried that this issue may creep up again. I figured someone had probably dealt with something like this before.

Hi RAG2007,

Neither am I! Rag if you don't know where that SQL is coming from there is not much chance of others knowing either and contributing something meaningful unless there is more detail regarding the potential source code causing it?

(Minion I love your signature by the way hahaha)

Jim

[Jim Doherty]

I'm creating a Project ID number based on the information of the currently entered project. That ID is based off of a code corresponding to the client company we work with. I have to pull up related that code from a separate table not in the recordsource. I was able to do this when Access was the backend, because access saves the record as soon as info is entered; SQL Server doesn't seem to do that, so I need a way to save the record to then use that record's info to pull up other data. When I used accmdSaveRecord , I got the error, including that really wierd select statement which I have no idea where it comes from.

Does that clarify? I could add more code to illustrate...

I actually rebuilt the entire form and reworked the way I was doing it, so the workaround is OK. But I'm worried that this issue may creep up again. I figured someone had probably dealt with something like this before.

Access doesn't save a record as soon as you enter something unless you tell it to in code, it saves on moving to another record. The 'pencil' tentative marker is an indicator of that. SQL server visually reacts in the same way as Access saving records in an ADP if things are behaving correctly (ie: If you move to the next record or again, if you tell it to save via code in the forms properties or some other control event.)

I use ADP's all the time and for me visually there is no appreciable difference between the response in and MDB and an ADP (again if tihngs are behaving themselves) besides having to be aware that records are on the server not in the client access application and thus an element of resynchronisati on might be required to avail server changes to the client.

If the form has a tentative un-saved record state then the forms DIRTY property is true and switching that to FALSE in code serves to save the record.

IF Me.Dirty=True Then Me.Dirty =False

Depending on the setup of your forms recordsource you may need to look at the RESYNCH property setting of the form in help? which ensures that record edits on the server side are reflected in the form. Have you used that yet? if not have a look at it it might need resynching

An example of a typical resynch command in the on current event / after insert event of the form might be something like this

Me.ResyncComman d = "Select * from dbo.qryMyView where RecordID=" & Me!RecordID

where dbo.qryMyView is your forms recordsource and you are resynching to ensure that any changes are reflected in the form

Regards

Jim :)