Please help with timing issue ?

[vamoose]

I am using Access 2000 here. What I am doing is this:

1. Open up a Master database
2. Import a table from another database to the Master database using DoCmd.Transfer Database as Table1
3. Copy this imported Table1 within the Master database as Table2 using DoCmd.CopyObject
4. Run a query to renumber the data in Table2
5. Open Form1 which uses a query to order by the newly renumbered data and display it in Listbox1 from Table2 on Form1

The problem I am having is that the newly copied and renumbered data in Table2 is not showing up in my ListBox1 on Form1. I do get the data but it is not correctly renumbered. If I open Table2 and view the data it is correctly numbered the way it should be. I think the process is happening too fast for Form1 and Listbox1 to grab the newly formatted data before Form1 opens. Is there a way to ensure that the copy and renumber process is totally completed before opening Form1 and ListBox1 to display the correct data. Thank you for any suggestions.

 

[PHV]

What is your actual code ?
it is not correctly renumbered
could you please elaborate ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Crowley16]

presumably you've got all this in a single sub or function, if so, then the code will be executed in order, not consecutively.

you might just need to have a me.requery on the form

--------------------
Procrastinate Now!

 

[vamoose]

Yes, all code is in 1 Private Sub.
There is a column in the table which contains a record number. It is a number data type in which I can manipulate and renumber in order to change it's position in the ListBox on the form. Here is the code being used to open, copy and renumber if necessary. Thanks for helping me.

'All code below on Form frm_Start

Option Compare Database: Dim Line, Auto, Records As Integer: Dim SQLa, strFolderName As String
Dim conn As ADODB.Connection: Dim rs As ADODB.Recordset

Private Sub Filename_AfterUpdate()
'Drop old tables before import and copy
DoCmd.RunSQL "DROP TABLE tbl_Copy": DoCmd.RunSQL "DROP TABLE ABS_Daily"
'Import file into ABS_Daily based on selected Filename here
Path = "C:\Program Files\DailyABS\" & [Filename] & ".mdb"
DoCmd.TransferDatabase acImport, "Microsoft Access", Path, acTable, "ABS_Daily", "ABS_Daily", False
Execute = "C:\Windows\System32\Cmd.exe /C "
'Copy ABS_Daily table to new table tbl_Copy
DoCmd.CopyObject , "tbl_Copy", acTable, "ABS_Daily"
'Get line number from Scelestus program
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Scelestus\Scelestus.mdb" & ";" & _
"Persist Security Info=False": conn.Open: SQLa = "SELECT Line from ([tbl line index])"
Set rs = conn.Execute(SQLa, , adCmdText): Line = rs.Fields("Line").Value
rs.Close: Set rs = Nothing: conn.Close: Set conn = Nothing
'Delete all records but associated line for use with above from Copy
DoCmd.RunSQL "DELETE * from tbl_Copy Where Line <> " & Line
'Determine if Auto has data, if so then it has been edited, skip numbering
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\DailyABS\OrderSpec.mdb" & ";" & _
"Persist Security Info=False": conn.Open: SQLa = "SELECT Sum(Auto) as Auto from tbl_Copy"
Set rs = conn.Execute(SQLa, , adCmdText): Status = rs.Fields("Auto").Value
SQLa = "SELECT Count(Material) as Records from tbl_Copy"
Set rs = conn.Execute(SQLa, , adCmdText): Records = rs.Fields("Records").Value

'Check for Auto to match records
If Status > 0 Then
Again:
SQLa = "SELECT Auto from tbl_Copy where Auto = " & [Records]
Set rs = conn.Execute(SQLa, , adCmdText)
If rs.EOF Then
SQLa = "SELECT Count(Auto) as MT from tbl_Copy Where Auto = 0"
Set rs = conn.Execute(SQLa, , adCmdText): MT = rs.Fields("MT"): MT = MT - 1: RecNew = [Records] - MT

SQLa = "SELECT Distinct Material, Plant, [Target Quantity],Auto from tbl_Copy Where Auto = 0"
Set rs = conn.Execute(SQLa, , adCmdText)
M = rs.Fields("Material"): Pl = rs.Fields("Plant"): T = rs.Fields("Target Quantity")

SQLa = "Update tbl_Copy Set Auto = " & RecNew & " Where Material = '" & M & "' and Plant = '" & Pl & "' and [Target Quantity] = " & T & ""
Set rs = conn.Execute(SQLa, , adCmdText)
End If
If MT <> 0 Then GoTo Again:
Set rs = Nothing: conn.Close: Set conn = Nothing: GoTo Skip:
End If
rs.Close: conn.Close: Set rs = Nothing: Set conn = Nothing

'If Auto has no data then it is an original copy. Number all remaining records left after delete from Copy
Set WS = DBEngine.Workspaces(0): Set MyDb = WS.OpenDatabase("C:\Program Files\DailyABS\OrderSpec.mdb")
Set MYSet = MyDb.OpenRecordset("tbl_Copy"): Auto = 1
MYSet.MoveFirst: Do Until MYSet.EOF: MYSet.Edit: MYSet("Auto") = Auto
MYSet.Update: Auto = Auto + 1: MYSet.MoveNext: Loop
MYSet.Close: MyDb.Close: WS.Close: Set WS = Nothing: Set MYSet = Nothing: Set MyDb = Nothing
Skip:

DoCmd.OpenForm "frm_Schedule", , , stLinkCriteria

'Transfer data to frm_Schedule before closing frm_Start, all data manipulation complete
[Forms]![frm_Schedule]![Line] = [Line]: [Forms]![frm_Schedule]![Records] = [Records]: [Forms]![frm_Schedule]![Filename] = [Filename]
[Forms]![frm_Schedule]![Text501] = [Text501]: [Forms]![frm_Schedule]![Text502] = [Text502]: [Forms]![frm_Schedule]![Text503] = [Text503]
[Forms]![frm_Schedule]![Text504] = [Text504]: [Forms]![frm_Schedule]![Text505] = [Text505]: [Forms]![frm_Schedule]![Text506] = [Text506]
[Forms]![frm_Schedule]![Text441] = [Text441]: [Forms]![frm_Schedule]![Text475] = [Text475]: [Forms]![frm_Schedule]![TextQty] = [TextQty]
DoCmd.Close acForm, "frm_Start": DoCmd.SetWarnings True
End Sub

 

[PHV]

Why mixing DAO and ADO stuff ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vamoose]

Because I am a beginner VBA programmer and this is the only way I could figure out how to accomplish this task, and it works. Any suggestions are welcome. Thanks

 

[vamoose]

I have put a 5 second delay timer in the VBA code as the second to last line followed by a requery as:

Call Sleep(5000)
[Forms]![frm_Schedule]![List2].Requery

This now gives the code time to catch up I am guessing because the data in the List Box initially shows up incorrect but then requeries and displays correctly after 5 seconds.
Is there now a faster or more correct way to make this better ?

Thank you to all.