Compile Error corrections

  • Thread starter Thread starter Douglas J Steele
  • Start date Start date
I found some errors in the code [control names] and corrected in case that
was part of the problem. But still get the same compile problem.

Private Sub DetParts_LostFocus()
On Error GoTo Err_DetParts_LostFocus


SendKeys "{ESC}"

'add new detail record like existing one

Dim strNewRecRoNum As String
Dim intNewRecEntryNum As Integer
Dim intNewRecItemNum As Integer

Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Set rs = db.OpenRecordset("Select Top 1 * From OrderDetailTbl
Where OrderNumber = '" & Me!OrderNumber & "' And RepairNumber = " &
Me!RepairNumber & " Order By RepairPartCount Desc", dbOpenSnapshot)

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartCount) Select '" & Me!OrderNumber & "' as Expr1, " &
Me!RepairNumber + 1 & " as Expr2, " & rs!RepairPartCount & " as Expr3"

strNewRecRoNum = Me!OrderNumber
intNewRecEntryNum = Me!RepairNumber + 1
intNewRecItemNum = rs!RepairPartCount

Me.Requery
Me.RecordsetClone.FindFirst "OrderNumber = '" & strNewRecRoNum &
"' And RepairNumber = " & intNewRecEntryNum & " And RepairPartCount = " &
intNewRecItemNum
If Me.RecordsetClone.NoMatch Then
MsgBox "Program error. Can't find new record. Contact
programmer."
GoTo Exit_DetParts_LostFocus
End If
Me.Bookmark = Me.RecordsetClone.Bookmark


Exit_DetParts_LostFocus:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_DetParts_LostFocus:
MsgBox Err.Description & ": " & Str(Err.Number)
Resume Exit_DetParts_LostFocus
End Sub
 
That implies that you don't have a reference set to DAO.

Go into the VB Editor, select Tools | References from the menu, scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it (using the checkbox) then click
on OK to close the dialog.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Roy Brandon said:
User Defined Type Not Defined

Douglas J Steele said:
What are the compile errors you're getting?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Roy Brandon said:
I found some errors in the code [control names] and corrected in case
that
was part of the problem. But still get the same compile problem.

Private Sub DetParts_LostFocus()
On Error GoTo Err_DetParts_LostFocus


SendKeys "{ESC}"

'add new detail record like existing one

Dim strNewRecRoNum As String
Dim intNewRecEntryNum As Integer
Dim intNewRecItemNum As Integer

Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Set rs = db.OpenRecordset("Select Top 1 * From OrderDetailTbl
Where OrderNumber = '" & Me!OrderNumber & "' And RepairNumber = " &
Me!RepairNumber & " Order By RepairPartCount Desc", dbOpenSnapshot)

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartCount) Select '" & Me!OrderNumber & "' as Expr1,
" &
Me!RepairNumber + 1 & " as Expr2, " & rs!RepairPartCount & " as Expr3"

strNewRecRoNum = Me!OrderNumber
intNewRecEntryNum = Me!RepairNumber + 1
intNewRecItemNum = rs!RepairPartCount

Me.Requery
Me.RecordsetClone.FindFirst "OrderNumber = '" &
strNewRecRoNum &
"' And RepairNumber = " & intNewRecEntryNum & " And RepairPartCount = " &
intNewRecItemNum
If Me.RecordsetClone.NoMatch Then
MsgBox "Program error. Can't find new record. Contact
programmer."
GoTo Exit_DetParts_LostFocus
End If
Me.Bookmark = Me.RecordsetClone.Bookmark


Exit_DetParts_LostFocus:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_DetParts_LostFocus:
MsgBox Err.Description & ": " & Str(Err.Number)
Resume Exit_DetParts_LostFocus
End Sub
Click to expand...
Click to expand...
Click to expand...
 
User Defined Type Not Defined

Douglas J Steele said:
What are the compile errors you're getting?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Roy Brandon said:
I found some errors in the code [control names] and corrected in case
that
was part of the problem. But still get the same compile problem.

Private Sub DetParts_LostFocus()
On Error GoTo Err_DetParts_LostFocus


SendKeys "{ESC}"

'add new detail record like existing one

Dim strNewRecRoNum As String
Dim intNewRecEntryNum As Integer
Dim intNewRecItemNum As Integer

Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Set rs = db.OpenRecordset("Select Top 1 * From OrderDetailTbl
Where OrderNumber = '" & Me!OrderNumber & "' And RepairNumber = " &
Me!RepairNumber & " Order By RepairPartCount Desc", dbOpenSnapshot)

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartCount) Select '" & Me!OrderNumber & "' as Expr1,
" &
Me!RepairNumber + 1 & " as Expr2, " & rs!RepairPartCount & " as Expr3"

strNewRecRoNum = Me!OrderNumber
intNewRecEntryNum = Me!RepairNumber + 1
intNewRecItemNum = rs!RepairPartCount

Me.Requery
Me.RecordsetClone.FindFirst "OrderNumber = '" &
strNewRecRoNum &
"' And RepairNumber = " & intNewRecEntryNum & " And RepairPartCount = " &
intNewRecItemNum
If Me.RecordsetClone.NoMatch Then
MsgBox "Program error. Can't find new record. Contact
programmer."
GoTo Exit_DetParts_LostFocus
End If
Me.Bookmark = Me.RecordsetClone.Bookmark


Exit_DetParts_LostFocus:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_DetParts_LostFocus:
MsgBox Err.Description & ": " & Str(Err.Number)
Resume Exit_DetParts_LostFocus
End Sub
Click to expand...
Click to expand...
 
Sorry, here is where it crashes

At dim db As Database Compile error User Defined Type Not Defined
 
wow, glad I broke down and ask. I would have been retired and still not have
fixed that!!

Douglas J Steele said:
That implies that you don't have a reference set to DAO.

Go into the VB Editor, select Tools | References from the menu, scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it (using the checkbox) then
click
on OK to close the dialog.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Roy Brandon said:
User Defined Type Not Defined

Douglas J Steele said:
What are the compile errors you're getting?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I found some errors in the code [control names] and corrected in case
that
was part of the problem. But still get the same compile problem.

Private Sub DetParts_LostFocus()
On Error GoTo Err_DetParts_LostFocus


SendKeys "{ESC}"

'add new detail record like existing one

Dim strNewRecRoNum As String
Dim intNewRecEntryNum As Integer
Dim intNewRecItemNum As Integer

Dim db As Database
Set db = CurrentDb()
Dim rs As Recordset
Set rs = db.OpenRecordset("Select Top 1 * From OrderDetailTbl
Where OrderNumber = '" & Me!OrderNumber & "' And RepairNumber = " &
Me!RepairNumber & " Order By RepairPartCount Desc", dbOpenSnapshot)

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartCount) Select '" & Me!OrderNumber & "' as Expr1,
"
&
Me!RepairNumber + 1 & " as Expr2, " & rs!RepairPartCount & " as Expr3"

strNewRecRoNum = Me!OrderNumber
intNewRecEntryNum = Me!RepairNumber + 1
intNewRecItemNum = rs!RepairPartCount

Me.Requery
Me.RecordsetClone.FindFirst "OrderNumber = '" &
strNewRecRoNum
&
"' And RepairNumber = " & intNewRecEntryNum & " And RepairPartCount =
" &
intNewRecItemNum
If Me.RecordsetClone.NoMatch Then
MsgBox "Program error. Can't find new record.
Contact
programmer."
GoTo Exit_DetParts_LostFocus
End If
Me.Bookmark = Me.RecordsetClone.Bookmark


Exit_DetParts_LostFocus:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_DetParts_LostFocus:
MsgBox Err.Description & ": " & Str(Err.Number)
Resume Exit_DetParts_LostFocus
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Douglas J Steele said:
That implies that you don't have a reference set to DAO.

Go into the VB Editor, select Tools | References from the menu, scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it (using the checkbox) then
click
on OK to close the dialog.
Click to expand...


----- Original Message -----
From: "Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com>
Newsgroups: microsoft.public.access.forms
Sent: Wednesday, July 05, 2006 1:46 PM
Subject: Re: Compile Error corrections

That implies that you don't have a reference set to DAO.

Go into the VB Editor, select Tools | References from the menu, scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, select it (using the checkbox) then
click
on OK to close the dialog.
Click to expand...


That corrected the compile error, thank you much, but the process still
does not work. I do not get any error reported, just a tone from the machine
like it didn't like it.
I have made some more changes to the code attempting to correct it.
The help you guys give to everyone is sure appreciated here, I have learned
a lot reading through the messages. Just seems like the more I work at this
the more complicated it gets. I'm in way over my head, but somebody's gota
do it.


orderNumber=same, all belong to this order
repairNumber=repairNumber+1, this is a new repair line item
repairPartNumber=1, this is the first part for this repair item

Private Sub DetParts_LostFocus()

On Error GoTo Err_DetParts_LostFocus

SendKeys "{ESC}"

'add new detail record like existing one

Dim strNewRecOrderNumber As String
Dim intNewRecRepairNumber As Integer
Dim intNewRecRepairPartNumber As Integer

Dim db As Database

Set db = CurrentDb()
Dim rs As Recordset
Set rs = db.OpenRecordset("Select Top 1 * From OrderDetailTbl
Where OrderNumber = '" & Me!OrderNumber & "' And RepairNumber = " &
Me!RepairNumber & " Order By RepairPartNumber Desc", dbOpenSnapshot)

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartNumber) Select '" & Me!OrderNumber & "' as Expr1, "
& Me!RepairNumber + 1 & " as Expr2, " & rs!RepairPartNumber & " as Expr3"

strNewRecOrderNumber = Me!OrderNumber
intNewRecRepairNumber = Me!RepairNumber + 1
intNewRecRepairPartNumber = rs!RepairPartNumber

Me.Requery
Me.RecordsetClone.FindFirst "OrderNumber = '" &
strNewRecOrderNumber & "' And RepairNumber = " & intNewRecRepairNumber & "
And RepairPartNumber = " & intNewRecRepairPartNumber
If Me.RecordsetClone.NoMatch Then
MsgBox "Program error. Can't find new record. Contact
programmer."
GoTo Exit_DetParts_LostFocus
End If
Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_DetParts_LostFocus:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_DetParts_LostFocus:
MsgBox Err.Description & ": " & Str(Err.Number)
Resume Exit_DetParts_LostFocus

End Sub
 
Your INSERT INTO SQL is incorrect. Try:

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartNumber) Values( '" & Me!OrderNumber & "', "
& Me!RepairNumber + 1 & " , " & rs!RepairPartNumber & ")"

Actually, if you use this, a trappable error message will be raised in
circumstances like this:

db.Execute "Insert Into OrderDetailTbl (OrderNumber,
RepairNumber, RepairPartNumber) Values( '" & Me!OrderNumber & "', "
& Me!RepairNumber + 1 & " , " & rs!RepairPartNumber & ")", dbFailOnError
 
Back
Top