Problem using DAO

J

JKarchner

I found this code online and modified to work with my database. However i
get an error saying: "Can't find project or library" on the first line with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
 
R

ruralguy via AccessMonster.com

While looking at your code go to Tools>References... and scroll down to
Microsoft DAO 3.x Object Library and put a check in it.
I found this code online and modified to work with my database. However i
get an error saying: "Can't find project or library" on the first line with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
Click to expand...
 
J

JKarchner

thank you, that solved that problem. however i am getting the same error in
a different location now. the error now occurs on line:

Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

any help you can provide would be great. thank you

ruralguy via AccessMonster.com said:
While looking at your code go to Tools>References... and scroll down to
Microsoft DAO 3.x Object Library and put a check in it.
I found this code online and modified to work with my database. However i
get an error saying: "Can't find project or library" on the first line with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
Click to expand...
Click to expand...
 
J

JKarchner

sorry the code is actually supposed to be:
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

but it still doesnt work

JKarchner said:
thank you, that solved that problem. however i am getting the same error in
a different location now. the error now occurs on line:

Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

any help you can provide would be great. thank you

ruralguy via AccessMonster.com said:
While looking at your code go to Tools>References... and scroll down to
Microsoft DAO 3.x Object Library and put a check in it.
I found this code online and modified to work with my database. However i
get an error saying: "Can't find project or library" on the first line with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 
J

JKarchner

OK i made some changes to what i had and it works. As you can tell i took
out the dbOpenDynaset from the code and it works perfect. How important is
that little bit of code and is it needed? Note:both "SQL =" lines are typed
on the same line

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID FROM qryWWindowsXREFModels WHERE
qryWWindowsXREFModels.Model_ID = " & Me.Model_ID & " AND
qryWWindowsXREFModels.SupplyPart_ID = " & Me.SupplyPart_ID & ";"
'Set rst = db.OpenRecordSet(SQL, dbOpenDynaset)
Set rst = db.OpenRecordset(SQL)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

JKarchner said:
sorry the code is actually supposed to be:
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

but it still doesnt work

JKarchner said:
thank you, that solved that problem. however i am getting the same error in
a different location now. the error now occurs on line:

Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

any help you can provide would be great. thank you

ruralguy via AccessMonster.com said:
While looking at your code go to Tools>References... and scroll down to
Microsoft DAO 3.x Object Library and put a check in it.

JKarchner wrote:
I found this code online and modified to work with my database. However i
get an error saying: "Can't find project or library" on the first line with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Check your SQL. You're writing it as multiple lines, and you've forgotten to
put spaces between each line, so that it results in:

SELECT Model_ID, SupplyPart_IDFROM qryWWindowsXREFModelsWHERE
qryWWindowsXREFModels.Model_ID = ...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JKarchner said:
sorry the code is actually supposed to be:
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

but it still doesnt work

JKarchner said:
thank you, that solved that problem. however i am getting the same error
in
a different location now. the error now occurs on line:

Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

any help you can provide would be great. thank you

ruralguy via AccessMonster.com said:
While looking at your code go to Tools>References... and scroll down to
Microsoft DAO 3.x Object Library and put a check in it.

JKarchner wrote:
I found this code online and modified to work with my database.
However i
get an error saying: "Can't find project or library" on the first line
with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID,
SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Since you corrected the error with your SQL that I pointed out, you should
be able to use the dbOpenDynaset parameter now if you want. To be honest,
though, I don't think it really matters that much.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


JKarchner said:
OK i made some changes to what i had and it works. As you can tell i took
out the dbOpenDynaset from the code and it works perfect. How important
is
that little bit of code and is it needed? Note:both "SQL =" lines are
typed
on the same line

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID FROM qryWWindowsXREFModels WHERE
qryWWindowsXREFModels.Model_ID = " & Me.Model_ID & " AND
qryWWindowsXREFModels.SupplyPart_ID = " & Me.SupplyPart_ID & ";"
'Set rst = db.OpenRecordSet(SQL, dbOpenDynaset)
Set rst = db.OpenRecordset(SQL)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID, SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

JKarchner said:
sorry the code is actually supposed to be:
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

but it still doesnt work

JKarchner said:
thank you, that solved that problem. however i am getting the same
error in
a different location now. the error now occurs on line:

Set rst = db.OpenRecordSet(SQL, dbOpenDynset)

any help you can provide would be great. thank you

:

While looking at your code go to Tools>References... and scroll down
to
Microsoft DAO 3.x Object Library and put a check in it.

JKarchner wrote:
I found this code online and modified to work with my database.
However i
get an error saying: "Can't find project or library" on the first
line with
DAO in it. My code is below, can anyone help me?

Private Sub Form_AfterInsert()
If Me.SupplyPartCategory = "Window" Then
Dim SQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strQuote As String
strQuote = Chr$(34)
Set db = CurrentDb()
SQL = "SELECT Model_ID, SupplyPart_ID" _
& "FROM qryWWindowsXREFModels" _
& "WHERE qryWWindowsXREFModels.Model_ID =" _
& strQuote & Me.Model_ID & strQuote _
& "AND qryWWindowsXREFModels.SupplyPart_ID =" _
& strQuote & Me.SupplyPart_ID & strQuote & ";"
Set rst = db.OpenRecordSet(SQL, dbOpenDynset)
If rst.RecordCount = 0 Then
SQL = "INSERT INTO qryWWindowsXREFModels (Model_ID,
SupplyPart_ID)
VALUES (" & Me.Model_ID & ", " & Me.SupplyPart_ID & ")"
DoCmd.RunSQL SQL
Me.Parent.[subFrmWQryWindows(New)].Form.Requery
End If
End If
End Sub

Thank you for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Deletion Problem 1
Search for record in another table for update 0
Trying to avoid repeats customer names 3
Difference 97 and 2003 using Shell and built-in logon form 3
Access 2003 and SQL Server 2005 and DAO recordset 1
Using GetRows 4
Do/Loop Dilemma 6
using sql server function 2

Top