Error after converting to SQL with dbsVBA.OpenRecordset

[fdalmoro]

Newbie alert.

Q#1. I used code from here

http://www.simply-access.com/SimplyAccess_Tips-Not-In-List-Event.html

to automate the process for users to add new items to lookup tables. It worked fine (for a day) until I put the database into SQL and the tables are all linked to the SQL datasource.

Here is the exact error that I get when I input something that's not in the list...

*******
Run-time error '3622':

You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column.

*******

When I click the Debug button it takes me to the VB window and highlights this row...

    Set rstKeyWord = dbsVBA.OpenRecordset("LU_EMPLOYEES")

The whole code can be seen at the link I provided before and I did not make any modifications except to change the correspondig table names etc...

Q#2. Also, I added a line that calls up the form to edit the entry in the lookup table, right after the code that I talked about before because for example the Employee name is added to the lookup table but none of the other information is updated, ie phone, department, location. This is the code.

        DoCmd.OpenForm "LU_EMPLOYEES", acNormal, , , , acDialog, "GotoLast"

Which I stole from another database so I have no idea of why "GoToLast" does but I would hope it makes the just opened form go to the last record. It doesn't work. How do I make the form go to the last record which has the just added employee name?

Thanks for the help in advance.

 

[beetee]

have you by any chance tried using the 'dbSeeChanges' option appropriately in your dbsVBA.OpenRecordset("LU_EMPLOYEES&quot function call?

I'm not familiar with the syntax of OpenRecordset, but perhaps your development environment will provide suitable help about how to add that parameter.

 

[cmmrfrds]

The identity in SQL Server is similiar to AutoNumber in Access. The dbSeeChanges would allow the id to be passed for use in the program, similiar to how Access already has the AutoNumber available when needed.

Set rstKeyWord = dbsVBA.OpenRecordset("LU_EMPLOYEES",dbSeeChanges)

 

[fdalmoro]

I tried...

Set rstKeyWord = dbsVBA.OpenRecordset("LU_EMPLOYEES",dbSeeChanges)

but it now gives me this error,

******************
Run-Time error '3001':

Invalid Argument.
******************

and then points me to the set rstKeyWord... line.

Any suggestions? I started using VB in Access this week so as you can see I have no clue. I just need this to work then I'm set.

Thanks

 

[beetee]

It's necessary to see how you declared the variables before we can proceed further.

That is, how did you declare rstKeyWord, and, more importantly, dbsVBA.

The InvalidArgument statement indicates that either "LU_EMPLOYEES" or dbSeeChanges do not match what the visual basic interpreter expects as arguments to the OpenRecordset command.

One thing that may prove helpful would be to read the Access help on the OpenRecordset method for whatever you have declared dbsVBA as.


You can do that by double clicking on OpenRecordset (to highlight), then pressing F1. If you can't find appropriate help that way, go to the declaration of dbsVBA, double click on its datatype, and press F1,

e.g.

dim dbsVGA as Database

 

[fdalmoro]

Hey.. don't ask me how but for some reason it's working now. I put it back the same way it was at the beginning and now I don't get any errors. Maybe it was a problem with the database. There are a couple of people making changes and they might have done it and put it back now.

Any idea how to get Q#2 to work?

 

[beetee]

My guess on Q2 is that the "GoToLast" line is an 'open arg'. To find out how it works you can go back to the database from which you obtained the code and look at the code for the form being opened.

You could implement it something like this (off the top of my head):

private sub Form_Load()
select case me.openargs
case "GoToLast"
docmd.gotorecord ,aclast
end select
end sub


You use the Load event instead of the open event so that there is data in the form, it doesn't make sense to goto last until the form is opened.

I may have misspelled a constant or left a comma out somewhere, but it should lead you down a reasonable path