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

[news.microsoft.com]

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

 

[Brendan Reynolds]

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.

 

[news.microsoft.com]

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

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)

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

 

[news.microsoft.com]

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

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

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)

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