What is wrong with this recordset?

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

What is the problem??? I get a type mismatch....can't figure it out!


Dim rec_OriginalRecord As Recordset
Dim str_Rec As String

str_Rec = "SELECT Meetings.*, Meetings.MeetingID FROM Meetings WHERE
Meetings.MeetingID = 1 "
Set rec_OriginalRecord = str_Rec

txb_Test.Value = rec_OriginalRecord!MeetingID

(MeetingID is a field in the Meetings table)
 
Jeff said:
What is the problem??? I get a type mismatch....can't figure it out!


Dim rec_OriginalRecord As Recordset
Dim str_Rec As String

str_Rec = "SELECT Meetings.*, Meetings.MeetingID FROM Meetings WHERE
Meetings.MeetingID = 1 "
Set rec_OriginalRecord = str_Rec

txb_Test.Value = rec_OriginalRecord!MeetingID

(MeetingID is a field in the Meetings table)
Click to expand...

If MeetingID is a text value the one must be in quotes.

Your str_Rec is a string and you must run that string as a query to return a
recordset.

"txb_Test.Value = rec_OriginalRecord!MeetingID" is not needed.
txb_Test.Value is always equal to 1 since that is what your string asks
for.

Meetings.* gets all the fields, there is no need for Meetings.MeetingID.
 
Jeff said:
What is the problem??? I get a type mismatch....can't figure it out!


Dim rec_OriginalRecord As Recordset
Dim str_Rec As String

str_Rec = "SELECT Meetings.*, Meetings.MeetingID FROM Meetings WHERE
Meetings.MeetingID = 1 "
Set rec_OriginalRecord = str_Rec

txb_Test.Value = rec_OriginalRecord!MeetingID

(MeetingID is a field in the Meetings table)
Click to expand...

You can't set a recordset equal to a string, even if that string does
contain a valid SQL SELECT statement. You have to actually open the
recordset. How you do this depends on whether you want to work with a
DAO or and ADODB recordset. If you're are querying a table in an Access
..mdb file, DAO is more efficient and more flexible, so here's DAO code
to do what you appear to be trying:

'----- start of code -----
Dim rec_OriginalRecord As DAO.Recordset
Dim str_Rec As String

str_Rec = "SELECT Meetings.* FROM Meetings " & _
"WHERE Meetings.MeetingID = 1"

Set rec_OriginalRecord = CurrentDb.OpenRecordset(str_Rec)

If Not rec_OriginalRecord.EOF Then
txb_Test.Value = rec_OriginalRecord!MeetingID
End If

' Don't forget to close and destroy the recordset object
' when you're done.
rec_OriginalRecord.Close
Set rec_OriginalRecord = Nothing
'----- end of code -----

Note that I've explicitly declared the recordset object as
DAO.Recordset -- that's to prevent any confusion between DAO and ADODB
recordsets. You will need to have a reference set in your project's
references to the Microsoft DAO 3.x Object Library -- 3.6 if you're
using Access 2000 or later, 3.51 or 3.5 if you're using Access 97.
 
Jeff said:
Dirk, Thanks for the reply..
Am getting a error "Too few parameters. Expected 1".
Click to expand...

The only way you could get that message on the SQL statement you (or I)
posted is if there's a misspelled field name. Since that statement had
all the earmarks of a test statement, it may be that the statement
you're getting the error on is actually different. Maybe your query is
using a reference to a control on a form as a criterion? If that's the
case, it counts as a parameter and DAO doesn't resolve parameters for
you -- you have to supply values for them.

If you're building the SQL statement in code, the easiest way to do that
is to "build in" the value of the parameter as a literal value in the
SQL string. Post your actual code and I'll try to show you how to do
that.
 
Dim rec_OriginalRecord As Recordset
Dim str_Rec As String

str_Rec = "SELECT Meetings.*, Meetings.MeetingID FROM Meetings WHERE
Meetings.MeetingID = txb_OriginalMeetingID "
Set rec_OriginalRecord = str_Rec


txb_Test.Value = rec_OriginalRecord!MeetingID
 
Below is code that exists in a command button. How do I complete this code
to edit the record it finds and replace the StartDate with the
txb_StartDate.value?

Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT Meetings.MeetingID FROM Meetings WHERE
(((Meetings.MeetingID)=txb_ParentMeetingID.value));"
 
Jeff said:
Dim rec_OriginalRecord As Recordset
Dim str_Rec As String

str_Rec = "SELECT Meetings.*, Meetings.MeetingID FROM Meetings WHERE
Meetings.MeetingID = txb_OriginalMeetingID "
Set rec_OriginalRecord = str_Rec

txb_Test.Value = rec_OriginalRecord!MeetingID
Click to expand...

'----- start of revised code -----
Dim rec_OriginalRecord As DAO.Recordset
Dim str_Rec As String

str_Rec = _
"SELECT Meetings.* FROM Meetings " & _
"WHERE Meetings.MeetingID = " & Me!txb_OriginalMeetingID

Set rec_OriginalRecord = CurrentDb.OpenRecordset(str_Rec)

If Not rec_OriginalRecord.EOF Then
Me!txb_Test.Value = rec_OriginalRecord!MeetingID
End If

' Don't forget to close and destroy the recordset object
' when you're done.
rec_OriginalRecord.Close
Set rec_OriginalRecord = Nothing
'----- end of code -----
 
Jeff said:
Below is code that exists in a command button. How do I complete
this code to edit the record it finds and replace the StartDate with
the txb_StartDate.value?

Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT Meetings.MeetingID FROM Meetings WHERE
(((Meetings.MeetingID)=txb_ParentMeetingID.value));"
Click to expand...

That SQL statement doesn't include a StartDate field from the Meetings
table, so it isn't possible to update the field in the resulting
recordset without using a different SQL statement. Since you have to
change the SQL statement anyway, why not forget about using a recordset
and its Edit and Update methods to modify the record? Why not use an
update query instead? That will be much more efficient. It might look
like this:

'----- start of code -----
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = _
"UPDATE Meetings SET StartDate = " & _
Format(Me!txb_StartDate, "\#mm/dd/yyyy\#") & _
" WHERE MeetingID=" & Me!txb_ParentMeetingID;"

dbs.Execute strSQL, dbFailOnError

Set dbs = Nothing
'----- end of code -----

That is based on the assumption that either there is one record that
meets the query's WHERE condition, or you want to update all records
that meet that condition.
 
Dirk,
Fixed! Thanks for the help...
Jeff

Dirk Goldgar said:
That SQL statement doesn't include a StartDate field from the Meetings
table, so it isn't possible to update the field in the resulting
recordset without using a different SQL statement. Since you have to
change the SQL statement anyway, why not forget about using a recordset
and its Edit and Update methods to modify the record? Why not use an
update query instead? That will be much more efficient. It might look
like this:

'----- start of code -----
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = _
"UPDATE Meetings SET StartDate = " & _
Format(Me!txb_StartDate, "\#mm/dd/yyyy\#") & _
" WHERE MeetingID=" & Me!txb_ParentMeetingID;"

dbs.Execute strSQL, dbFailOnError

Set dbs = Nothing
'----- end of code -----

That is based on the assumption that either there is one record that
meets the query's WHERE condition, or you want to update all records
that meet that condition.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top