Access 97 Backend, Access 97 Client, ADO, VBA, SQL & a strange form problem...

Access 97 Backend, Access 97 Client, ADO, VBA, SQL & a strange form problem...

Post by news.micro » Wed, 22 Jun 2005 19:53:51


I have some code that runs within a form as below. The idea being that when
it has run and created a new entry in a table called tbl_jobs, a form called
frm_jobs that is bound to that table then opens to diapy just that specific
record. The trouble is that it doesn't work - it always display a new
record UNLESS I put a 5 second delay between after my_rs.close and
docmd.openform

ps. the field_job_number is an autonumber field
any ideas?

thanks

Jeff

'CREATE THE NEW JOB
Dim my_RS As ADODB.Recordset
Set my_RS = New ADODB.Recordset
Dim job_number As Long
my_RS.Open "[tbl_jobs]", my_db, adOpenKeyset, adLockOptimistic, adCmdTable
my_RS.AddNew
job_number = my_RS("field_job_number")
my_RS("Customer Code") = "fred bloggs"
my_RS("Destination") = "joe bloggs" ' need to request this somehow
my_RS("On Hold") = "QC"
my_RS("Style No") = "A123"
my_RS("Order No") = "ZY12"
my_RS("Creator") = "Auto"
my_RS("Advised No") = 1000
my_RS.update
my_RS.Close
MsgBox "Job " & job_number & " created"
DoCmd.OpenForm "frm_jobs", , , "[tbl_jobs].[field_job_number]=" & job_number
 
 
 

Access 97 Backend, Access 97 Client, ADO, VBA, SQL & a strange form problem...

Post by Brendan Re » Wed, 22 Jun 2005 21:20:16

You could try setting the connection to Nothing after writing the value and
before querying the table - I've seen some reports that this has sometimes
resolved these timing issues. But I would strongly advise not using ADO with
Access 97. Access 97 was never designed to work with ADO.

--
Brendan Reynolds (MVP)

 
 
 

Access 97 Backend, Access 97 Client, ADO, VBA, SQL & a strange form problem...

Post by news.micro » Wed, 22 Jun 2005 22:20:16

Thanks Brendan for your reply, I am most grateful as this problem is causing
me some major grief.

I have heard similar comments previously about using ADO in Access 97,
however on this occasion I can confirm I have tried the same code suing DAO,
and the same problem exists. (as a side point, it may be non advisable, but
most of the extensive work I have done on the database client in question is
using ADO)

Any more ideas on this strange behaviour?

Thanks




and
with


adCmdTable
 
 
 

Access 97 Backend, Access 97 Client, ADO, VBA, SQL & a strange form problem...

Post by news.micro » Wed, 22 Jun 2005 22:47:14

bizzarre - just tried DAO again - and it works! - thanks for your help




causing
DAO,
but
is


sometimes