Database Crashing

S

Sash

I'll try to make this short. I have a database where the user clicks a
button that allows the user to select a file and then runs a batch file that
monarchs a report and brings it into Access. After the data is loaded, I
then reformat the data and write it to a file. This is all done so that the
data can be in a certain format to import into a another system. Here's the
odd part and I even had a collegue watch me do it each way, because I thought
I was seeing things.

1) The button works when I'm in the database without opening it from the
desktop icon and having the mainform automatically display.

2) The button works perfectly when I try to step through and debug the
problem.

3) If I put message boxes throughout where I think it might be failing, it
works.

Otherwise, it goes through all the steps including creating the file to
write to...however, after that I get a message "Microsoft Access encountered
a problem and needs to close" the error code in the report is Oxc0000005 and
I've pasted my code where it seems to be failing.

Please bear with me. I'm all self taught and know that my code probably
isn't up to many of your standards. Of course this began to occur the day
before my vacation begins. :eek:(

Thanks for any input,
Sash


'***************************** FH - FILE HEADER
*********************************************************
Dim FH As String
FH = "FH" & Chr(9) & Date & Chr(9) & Time() & Chr(9) & strClient &
Chr(9) & "0"
Debug.Print , FH
Print #1, FH
rs.MoveFirst
Do While Not rs.EOF
On Error Resume Next
'****************************** HD - HEADER SEGMENT
****************************************************
Dim HD As String
Dim strCount As Integer
strCount = strCount + 1
HD = "HD" & Chr(9) & strClientNum & Chr(9)
If rs.Fields("PTNUM") <> strPTNum Then
HD = HD & rs.Fields("PTNUM") & Chr(9) & rs.Fields("PTNUM") &
Chr(9) & "U"
Debug.Print HD
Print #1, HD
strPTNum = rs.Fields("PTNUM")
End If
'******************************* NT - NOTE SEGMENT
****************************************************

With rs

If Not rs.BOF And Not rs.EOF Then

Dim NT As String

If Not IsNull(rs.Fields("COMMENT")) Then
NT = "NT" & Chr(9) & rs.Fields("NTDate") & Chr(9) &
rs.Fields("COMMENT")
Print #1, NT
Debug.Print , NT
End If

rs.MoveNext

End If
End With

Loop
Close #1
rs.Close
Me.[Frame82] = Null

MsgBox "Complete " & Time() & " Records Exported: " & strCount
DoCmd.SetWarnings True

Select Case strClientNum
'Move File to Archive
Case "220"
FileCopy NewNameFSN, "u:\interfaces\import\archive\FSN\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
MsgBox "File moved to Archive."
Kill "u:\interfaces\import\FSHN.txt"
Case "210"
FileCopy NewNameSN, "u:\interfaces\import\archive\SN\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
MsgBox "File moved to Archive."
Kill "u:\interfaces\import\SEQNote.txt"
End Select
 
D

Dave C

Not sure if this would cause the crash, but in this segment of code...
Dim strCount As Integer
strCount = strCount + 1

You're referencing the variable strCount without ever having assigned a
value to it. Usually, you see something like this...

Dim strCount as Integer
strCount = 1

.... some loop ...
strCount = strCount + 1
.... end of loop ...


Sash said:
I'll try to make this short. I have a database where the user clicks a
button that allows the user to select a file and then runs a batch file that
monarchs a report and brings it into Access. After the data is loaded, I
then reformat the data and write it to a file. This is all done so that the
data can be in a certain format to import into a another system. Here's the
odd part and I even had a collegue watch me do it each way, because I thought
I was seeing things.

1) The button works when I'm in the database without opening it from the
desktop icon and having the mainform automatically display.

2) The button works perfectly when I try to step through and debug the
problem.

3) If I put message boxes throughout where I think it might be failing, it
works.

Otherwise, it goes through all the steps including creating the file to
write to...however, after that I get a message "Microsoft Access encountered
a problem and needs to close" the error code in the report is Oxc0000005 and
I've pasted my code where it seems to be failing.

Please bear with me. I'm all self taught and know that my code probably
isn't up to many of your standards. Of course this began to occur the day
before my vacation begins. :eek:(

Thanks for any input,
Sash


'***************************** FH - FILE HEADER
*********************************************************
Dim FH As String
FH = "FH" & Chr(9) & Date & Chr(9) & Time() & Chr(9) & strClient &
Chr(9) & "0"
Debug.Print , FH
Print #1, FH
rs.MoveFirst
Do While Not rs.EOF
On Error Resume Next
'****************************** HD - HEADER SEGMENT
****************************************************
Dim HD As String
Dim strCount As Integer
strCount = strCount + 1
HD = "HD" & Chr(9) & strClientNum & Chr(9)
If rs.Fields("PTNUM") <> strPTNum Then
HD = HD & rs.Fields("PTNUM") & Chr(9) & rs.Fields("PTNUM") &
Chr(9) & "U"
Debug.Print HD
Print #1, HD
strPTNum = rs.Fields("PTNUM")
End If
'******************************* NT - NOTE SEGMENT
****************************************************

With rs

If Not rs.BOF And Not rs.EOF Then

Dim NT As String

If Not IsNull(rs.Fields("COMMENT")) Then
NT = "NT" & Chr(9) & rs.Fields("NTDate") & Chr(9) &
rs.Fields("COMMENT")
Print #1, NT
Debug.Print , NT
End If

rs.MoveNext

End If
End With

Loop
Close #1
rs.Close
Me.[Frame82] = Null

MsgBox "Complete " & Time() & " Records Exported: " & strCount
DoCmd.SetWarnings True

Select Case strClientNum
'Move File to Archive
Case "220"
FileCopy NewNameFSN, "u:\interfaces\import\archive\FSN\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
MsgBox "File moved to Archive."
Kill "u:\interfaces\import\FSHN.txt"
Case "210"
FileCopy NewNameSN, "u:\interfaces\import\archive\SN\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
MsgBox "File moved to Archive."
Kill "u:\interfaces\import\SEQNote.txt"
End Select
 
S

Sash

All....I'm back from vacation and tried this change and I'm still getting the
same error. Any thoughts would be greatly appreciate. I even tried to
create a new database end import all my tables and code. This customer needs
this all working my next Monday and I have no idea what the problem could be.

Dave C said:
Not sure if this would cause the crash, but in this segment of code...
Dim strCount As Integer
strCount = strCount + 1

You're referencing the variable strCount without ever having assigned a
value to it. Usually, you see something like this...

Dim strCount as Integer
strCount = 1

... some loop ...
strCount = strCount + 1
... end of loop ...


Sash said:
I'll try to make this short. I have a database where the user clicks a
button that allows the user to select a file and then runs a batch file that
monarchs a report and brings it into Access. After the data is loaded, I
then reformat the data and write it to a file. This is all done so that the
data can be in a certain format to import into a another system. Here's the
odd part and I even had a collegue watch me do it each way, because I thought
I was seeing things.

1) The button works when I'm in the database without opening it from the
desktop icon and having the mainform automatically display.

2) The button works perfectly when I try to step through and debug the
problem.

3) If I put message boxes throughout where I think it might be failing, it
works.

Otherwise, it goes through all the steps including creating the file to
write to...however, after that I get a message "Microsoft Access encountered
a problem and needs to close" the error code in the report is Oxc0000005 and
I've pasted my code where it seems to be failing.

Please bear with me. I'm all self taught and know that my code probably
isn't up to many of your standards. Of course this began to occur the day
before my vacation begins. :eek:(

Thanks for any input,
Sash


'***************************** FH - FILE HEADER
*********************************************************
Dim FH As String
FH = "FH" & Chr(9) & Date & Chr(9) & Time() & Chr(9) & strClient &
Chr(9) & "0"
Debug.Print , FH
Print #1, FH
rs.MoveFirst
Do While Not rs.EOF
On Error Resume Next
'****************************** HD - HEADER SEGMENT
****************************************************
Dim HD As String
Dim strCount As Integer
strCount = strCount + 1
HD = "HD" & Chr(9) & strClientNum & Chr(9)
If rs.Fields("PTNUM") <> strPTNum Then
HD = HD & rs.Fields("PTNUM") & Chr(9) & rs.Fields("PTNUM") &
Chr(9) & "U"
Debug.Print HD
Print #1, HD
strPTNum = rs.Fields("PTNUM")
End If
'******************************* NT - NOTE SEGMENT
****************************************************

With rs

If Not rs.BOF And Not rs.EOF Then

Dim NT As String

If Not IsNull(rs.Fields("COMMENT")) Then
NT = "NT" & Chr(9) & rs.Fields("NTDate") & Chr(9) &
rs.Fields("COMMENT")
Print #1, NT
Debug.Print , NT
End If

rs.MoveNext

End If
End With

Loop
Close #1
rs.Close
Me.[Frame82] = Null

MsgBox "Complete " & Time() & " Records Exported: " & strCount
DoCmd.SetWarnings True

Select Case strClientNum
'Move File to Archive
Case "220"
FileCopy NewNameFSN, "u:\interfaces\import\archive\FSN\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
MsgBox "File moved to Archive."
Kill "u:\interfaces\import\FSHN.txt"
Case "210"
FileCopy NewNameSN, "u:\interfaces\import\archive\SN\" & Format(Time,
"HHMMSS") & "_" & strOrigFile
MsgBox "File moved to Archive."
Kill "u:\interfaces\import\SEQNote.txt"
End Select
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top