NEEED EXPERTS HELP ASAP ***Keeps Crashing***

[rivers]

Hi All

I have created a program that transfers information from one record into 53
records in a subform.

the 1 record has 53 weeks in it, and for each week i need to create one
record in my subform.

the program works except. it needs to run through 1000 records and creates
over 53000 records (sounds alot but nessassary)

however when my screensave comes on or i Move to another screen it crashes
my program and reverts me back to square one!! can anyone help

Private Sub Command123_Click()
Dim i As Integer
Dim sel As String
Dim sel2 As Double
Dim sel3 As String
Dim i2 As Integer
Dim counttot As Integer
Dim check1 As Double

counttot = Form.Recordset.RecordCount
i2 = 1
i = 1

Do Until i2 = counttot
i = 1
sel3 = Forms![1 Run]![KPI CSV name].Value

'Application.Echo False
Do Until i = 54

Forms![1 Run]![Weekchk] = i
sel = "WK " & i
sel2 = Forms![1 Run].Controls(sel)
Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value = sel2
check1 = Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value
'Forms![1 Run]![UPLOAD KPI subform].SetFocus
'Forms![1 Run]!ID.SetFocus


i = i + 1
Loop
'Application.Echo True

'Forms![1 Run]!ID.SetFocus
DoCmd.GoToRecord , , acNext
i2 = i2 + 1
Loop


DoCmd.GoToRecord , , acFirst

End Sub

 

[Mr. B]

This looks like you are attempting to move through controls in the subform
and populate them. I am assuming that the subform has a table (or a query)
as its data source. The better approach is to open a record source using the
table that will hold the data, create all of the new records in this record
sorce and then just refresh (requery) your subform to display the new
records.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Jack Leach]

Try putting DoEvents inside your loop, either at the beginning or end. This
should hold up the code letting the system handle other things before
resuming.

Hopefully you're doing this in an attempt to better your data
normalization... it seems to be suffering.

You might also want to try using a recordset(s) rather than forms (or SQL
which would be even better, but I have no idea how to tackle this with SQL).
At least with recordsets you're not using the form interface for mass
transfers... the form interface is pretty process intensive. Access has a
lot of stuff running the the background doing it through forms. A recordset
would get rid of that and use just the basics.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[rivers]

Ok Guys ty for quick response

Agreed this is a cack handed approach of doing what i need but it does it.
(dont know how)

Ok heres my first table

unit code Type Wk1 Wk2 Wk 3 -----Wk 53
32434 Wages 3454 32 42342 23423
32434 sales 324 32 42342 23423


and this is what im transfering it to


Unit Week Sales Wages
32434 Wk1 324 3454


and my approach using forms sorts these for me

do you know of a better approach idf so guys i seriously am listening.

also found my VB works if i dont touch anything however the pesky
screensaver keeps kicking in and this is automated by server so i cant change
the time limit on it DOH!!

so i have to sit here twitching the mouse ever 100 records or so (Boring)

Try putting DoEvents inside your loop, either at the beginning or end. This
should hold up the code letting the system handle other things before
resuming.

Hopefully you're doing this in an attempt to better your data
normalization... it seems to be suffering.

You might also want to try using a recordset(s) rather than forms (or SQL
which would be even better, but I have no idea how to tackle this with SQL).
At least with recordsets you're not using the form interface for mass
transfers... the form interface is pretty process intensive. Access has a
lot of stuff running the the background doing it through forms. A recordset
would get rid of that and use just the basics.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Hi All

I have created a program that transfers information from one record into 53
records in a subform.

the 1 record has 53 weeks in it, and for each week i need to create one
record in my subform.

the program works except. it needs to run through 1000 records and creates
over 53000 records (sounds alot but nessassary)

however when my screensave comes on or i Move to another screen it crashes
my program and reverts me back to square one!! can anyone help

Private Sub Command123_Click()
Dim i As Integer
Dim sel As String
Dim sel2 As Double
Dim sel3 As String
Dim i2 As Integer
Dim counttot As Integer
Dim check1 As Double

counttot = Form.Recordset.RecordCount
i2 = 1
i = 1

Do Until i2 = counttot
i = 1
sel3 = Forms![1 Run]![KPI CSV name].Value

'Application.Echo False
Do Until i = 54

Forms![1 Run]![Weekchk] = i
sel = "WK " & i
sel2 = Forms![1 Run].Controls(sel)
Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value = sel2
check1 = Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value
'Forms![1 Run]![UPLOAD KPI subform].SetFocus
'Forms![1 Run]!ID.SetFocus


i = i + 1
Loop
'Application.Echo True

'Forms![1 Run]!ID.SetFocus
DoCmd.GoToRecord , , acNext
i2 = i2 + 1
Loop


DoCmd.GoToRecord , , acFirst

End Sub

 

[Jack Leach]

Agreed this is a cack handed approach of doing what i need but it does it.

(dont know how)

Forms are not made to be used like this... you can't seriously expect to be
able to rely on the integrity of your data when the system crashes if you so
much as highlight another window or the screen saver kicks in.

Obviously you got a very wrong approach here. Readup on some DAO recordsets
and SQL (ORDER BY) and do it the right way before you screw things up beyond
repair.


--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Ok Guys ty for quick response

Agreed this is a cack handed approach of doing what i need but it does it.
(dont know how)

Ok heres my first table

unit code Type Wk1 Wk2 Wk 3 -----Wk 53
32434 Wages 3454 32 42342 23423
32434 sales 324 32 42342 23423


and this is what im transfering it to


Unit Week Sales Wages
32434 Wk1 324 3454


and my approach using forms sorts these for me

do you know of a better approach idf so guys i seriously am listening.

also found my VB works if i dont touch anything however the pesky
screensaver keeps kicking in and this is automated by server so i cant change
the time limit on it DOH!!

so i have to sit here twitching the mouse ever 100 records or so (Boring)

Try putting DoEvents inside your loop, either at the beginning or end. This
should hold up the code letting the system handle other things before
resuming.

Hopefully you're doing this in an attempt to better your data
normalization... it seems to be suffering.

You might also want to try using a recordset(s) rather than forms (or SQL
which would be even better, but I have no idea how to tackle this with SQL).
At least with recordsets you're not using the form interface for mass
transfers... the form interface is pretty process intensive. Access has a
lot of stuff running the the background doing it through forms. A recordset
would get rid of that and use just the basics.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

Hi All

I have created a program that transfers information from one record into 53
records in a subform.

the 1 record has 53 weeks in it, and for each week i need to create one
record in my subform.

the program works except. it needs to run through 1000 records and creates
over 53000 records (sounds alot but nessassary)

however when my screensave comes on or i Move to another screen it crashes
my program and reverts me back to square one!! can anyone help

Private Sub Command123_Click()
Dim i As Integer
Dim sel As String
Dim sel2 As Double
Dim sel3 As String
Dim i2 As Integer
Dim counttot As Integer
Dim check1 As Double

counttot = Form.Recordset.RecordCount
i2 = 1
i = 1

Do Until i2 = counttot
i = 1
sel3 = Forms![1 Run]![KPI CSV name].Value

'Application.Echo False
Do Until i = 54

Forms![1 Run]![Weekchk] = i
sel = "WK " & i
sel2 = Forms![1 Run].Controls(sel)
Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value = sel2
check1 = Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value
'Forms![1 Run]![UPLOAD KPI subform].SetFocus
'Forms![1 Run]!ID.SetFocus


i = i + 1
Loop
'Application.Echo True

'Forms![1 Run]!ID.SetFocus
DoCmd.GoToRecord , , acNext
i2 = i2 + 1
Loop


DoCmd.GoToRecord , , acFirst

End Sub

 

[CraigH]

Hi,

To me it looks like what is happening is that you are normalizing the first
table. Unfortunalty since as you said you don't know how the code works (if
others are interesed I have Inline comments about what I think is going on -
just did it as a lark) you may have trouble with what I advise - But it is
the way to do this without the Forms.

Your first table is being normalized into the Second Table, and one way to
do this is through recordsets.

Not complete code but

Private Sub Command123_Click() ' or another
Dim dbs As DAO.Database
Dim rstFirst As DAO.Recorset
' other decares and

'Set up the recordsets
set dbs = CurrentDB

strSQL = Select * From tblFirstSales WHERE Type = 'Sales' ' you may also
have to limit this to the Unit codes that you need because the form may have
done some of that itself
set rstFirstSales = dbs.Openrecorset(strSQL)

strSQL = Select * From tblFirstWages WHERE Type = 'Wages' ' you may also
have to limit this to the Unit codes that you need because the form may have
done some of that itself
set rstFirstSales = dbs.Openrecorset(strSQL)

strSQL = Select * From tlbSecond
set rstSecond = dbs.OpenRecordset

With rstFirstSales
.MoveFirst ' realy should check for records but...

Do while not .EOF
' find the same Unit for the Wages
rstFirstWages.Findfirst "[Unit Code] = ' " & ![Unit Code] & " ' "
' take out single quotes if this is really a number
' Really should check to see if it has one but...

With rstSecond
For intLoop = 1 to 53
.Addnew
!Unit = rstFirstSales![Unit Num]
!Week = "WK" & IntLoop
!Sales = rstFirstSales!("WK" & IntLoop") 'this should
be the Field name and see inline notes
!Wages = rstFirstSales!("WK" & IntLoop")
.Update
Next intLoop
End with
.MoveNext ' Sales
Loop
End With

This is a start - there are issues if you don't have a both the wage and
Sales for the same unit.

In english: Take a recordset of only Sales and one of only Wages. Loop
through the Sales and find the match in Wages. Add to the Second Table
through a looping process 53 times to add the 4 colums of Unit, Week, the
sales and Wages.

There are still problems with this because for the second table you have no
year so what year would it be? you can only do 1 year.


And for how your current code is working - Just for gigles.

Ok Guys ty for quick response

Agreed this is a cack handed approach of doing what i need but it does it.
(dont know how)

Ok heres my first table

unit code Type Wk1 Wk2 Wk 3 -----Wk 53
32434 Wages 3454 32 42342 23423
32434 sales 324 32 42342 23423

If the WK1 has no space and Wk 3 really does then the code above will need
to account for this.

and this is what im transfering it to


Unit Week Sales Wages
32434 Wk1 324 3454


and my approach using forms sorts these for me

do you know of a better approach idf so guys i seriously am listening.

also found my VB works if i dont touch anything however the pesky
screensaver keeps kicking in and this is automated by server so i cant change
the time limit on it DOH!!

so i have to sit here twitching the mouse ever 100 records or so (Boring)

Taken the count of all records

Loop untill all records are done

i = 1
sel3 = Forms![1 Run]![KPI CSV name].Value

Get the name of the Type (Wages or Sales)
Loop through the 54 WK controls on the Form

Forms![1 Run]![Weekchk] = i

Guessing this is used to make the suborm change to the particlular week - it
can only be 1 week for a particlular unit that is shown in the subform - not
all 53 weeks for a unit.

So the parent Child Relation is Unit Num; Weekchk to Unit; Week

sel = "WK " & i
sel2 = Forms![1 Run].Controls(sel)

Sel2 is the value in (WK I)

Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value = sel2

Because of the relationship above the Unit and Week is defaulted into the
subform and the Sel3 shows wich of either Sales Or Wages to put the value

check1 = Forms![1 Run]![UPLOAD KPI subform].Controls(sel3).Value

Possibly puts this back into a textbox on the main form???

'Forms![1 Run]![UPLOAD KPI subform].SetFocus
'Forms![1 Run]!ID.SetFocus


i = i + 1
Loop
'Application.Echo True

'Forms![1 Run]!ID.SetFocus
DoCmd.GoToRecord , , acNext
i2 = i2 + 1
Loop


DoCmd.GoToRecord , , acFirst

End Sub

 

[Steve Sanford]

For grins (also).... My version.... Modify to suit your requirements.

The old table has fields "[Unit code]", "Type", and "WK1" thru "WK53"
and there are NO spaces between the "WK" and the number.


The new table fields are: Unit, Week, Sales, Wages

"Unit", "Sales" and 'Wages" are field type Long Integer.
"Week" field type is Text


Note: change the table names. See just below the declarations
'------------------------------------------------
Private Sub Command123_Click()
'my first table
'unit code Type Wk1 Wk2 Wk 3 -----Wk 53
'32434 Wages 3454 32 42342 23423
'32434 sales 324 32 42342 23423
'
'
'and this is what im transfering it to
'
'Unit Week Sales Wages
'32434 Wk1 324 3454

Dim d As DAO.Database
Dim rSales As DAO.Recordset
Dim rWages As DAO.Recordset
Dim rUnit As DAO.Recordset
Dim sSQL As String
Dim vWeekNum As String
Dim w As Integer
Dim vUnitNum As Long
Dim vS_Amt As Long
Dim vW_Amt As Long
Dim TableOld As String
Dim TableNew As String


'CHANGE 'tblFirstTable' to your FIRST (OLD) table name
'CHANGE 'tblSecondTable' to your SECOND (NEW) table name

'************************************
TableOld = "tblFirstTable"
TableNew = "tblSecondTable"
'*************************************


Set d = CurrentDb

'get the unique Unit Codes
sSQL = "SELECT DISTINCT [Unit code] "
sSQL = sSQL & " FROM " & TableOld & " ORDER BY [Unit code]"
Set rUnit = d.OpenRecordset(sSQL)
rUnit.MoveLast

'loop thru the Unit Codes
If Not rUnit.BOF And Not rUnit.EOF Then
rUnit.MoveFirst
Do While Not rUnit.EOF
'unit code
vUnitNum = rUnit.Fields(0)
'open a recordset for Sales for the Unit code
sSQL = "Select * From " & TableOld
sSQL = sSQL & " WHERE Type = 'Sales' AND [Unit code] = " & vUnitNum
Set rSales = d.OpenRecordset(sSQL)
If Not rSales.EOF Then
rSales.MoveLast
End If

'open a recordset for Wages for the Unit code
sSQL = "Select * From " & TableOld
sSQL = sSQL & " WHERE Type = 'Wages' AND [Unit code] = " & vUnitNum
Set rWages = d.OpenRecordset(sSQL)
If Not rWages.EOF Then
rWages.MoveLast
End If

'check for a Sales record that matches the Wages record
' by Unit code
If rSales.RecordCount <> 1 Or rWages.RecordCount <> 1 Then
'we have an error
' YOU need to record this somewhere
If rSales.RecordCount <> 1 Then
MsgBox "Missing or Wrong number of records - Unit " &
vUnitNum & " for Sales "
End If
If rWages.RecordCount <> 1 Then
MsgBox "Missing or Wrong number of records - Unit " &
vUnitNum & " for Wages "
End If
Else
' Matching unit codes found
'yea, I know ... but it is a habit
rSales.MoveFirst
rWages.MoveFirst

'loop thru the weeks
For w = 1 To 53
vWeekNum = "WK" & w
vS_Amt = Nz(rSales.Fields("WK" & w), 0)
vW_Amt = Nz(rWages.Fields("WK" & w), 0)
' and insert them into the new table
sSQL = "INSERT INTO " & TableNew & " (Unit, Week, Sales,
Wages)"
sSQL = sSQL & " VALUES(" & vUnitNum & ", '" & vWeekNum & "', "
sSQL = sSQL & vS_Amt & ", " & vW_Amt & ");"
' Debug.Print sSQL
d.Execute sSQL, dbFailOnError
Next w
rSales.Close
rWages.Close
End If
' move to the next unit code
rUnit.MoveNext
Loop
End If

'clean up & exit
On Error Resume Next
rSales.Close
rWages.Close
rUnit.Close

Set rSales = Nothing
Set rWages = Nothing
Set rUnit = Nothing
Set d = Nothing
MsgBox "DONE!"
End Sub
'------------------------------------------------

HTH