Do I have the right idea about using recordsets?

  • Thread starter Thread starter Dickie Black
  • Start date Start date
D

Dickie Black

Hi,

I'd like to set the caption for a group of labels on an unbound form
depending on the presence of a record in a table. That is, if a record
exists for Monday 12th July at 9:30 AM I would like for a box to show one of
the fields from that record.

I have tried the following:

'create recordset for week described in week commencing combo
Dim rs As DAO.Recordset
Dim SQLString As String

SQLString = "SELECT * FROM [Session] WHERE ((([Session].[Date]) Between " &
DateMon & " AND " & DateFri & "));"
Set rs = CurrentDb.OpenRecordset(SQLString)

'set values for boxes to relevant record
rs.FindFirst ("[Date] = DateMon And [StartTime] = Session1")
Me.Session_1_1.Caption = [Title]

'close recordset
Set rs = Nothing

where the values for Session1, DateMon and DateFri are set earlier in the
code.

I know the line Me.Session_1_1.Caption = [Title] does not work, but think it
gives an impression of what I'm trying to achieve. I want to find a record
that occurs at the time and date specified then set the caption of the label
to the value of the [Title] field. Am I going about this correctly?

TIA,

Dickie Black
 
Thanks,

I've just tried rs!Title, but get an error message: "No current record" Is
there a line needed after the find first statement ?

Also, the date variables have the # character inserted into them earlier in
the code so that shouldn't be a problem


Dan Artuso said:
Hi,
You have to delimit dates with #
WHERE ((([Session].[Date]) Between #" &
DateMon & "# AND #" & DateFri & "#));"

Then to refer to a field in the rs use:
rs!NameOfField

Date is also a bad choice for a field name as it's also a function.


--
HTH
Dan Artuso, Access MVP


Hi,

I'd like to set the caption for a group of labels on an unbound form
depending on the presence of a record in a table. That is, if a record
exists for Monday 12th July at 9:30 AM I would like for a box to show one of
the fields from that record.

I have tried the following:

'create recordset for week described in week commencing combo
Dim rs As DAO.Recordset
Dim SQLString As String

SQLString = "SELECT * FROM [Session] WHERE ((([Session].[Date]) Between " &
DateMon & " AND " & DateFri & "));"
Set rs = CurrentDb.OpenRecordset(SQLString)

'set values for boxes to relevant record
rs.FindFirst ("[Date] = DateMon And [StartTime] = Session1")
Me.Session_1_1.Caption = [Title]

'close recordset
Set rs = Nothing

where the values for Session1, DateMon and DateFri are set earlier in the
code.

I know the line Me.Session_1_1.Caption = [Title] does not work, but think it
gives an impression of what I'm trying to achieve. I want to find a record
that occurs at the time and date specified then set the caption of the label
to the value of the [Title] field. Am I going about this correctly?

TIA,

Dickie Black
Click to expand...
Click to expand...
 
Hi,
The criteria part of the FindFirst is a Where clause,
so you have to build it the same way you built your SQL.

rs.FindFirst ("[Date] = " & DateMon & " And [StartTime] =" & Session1)
Use the correct delimiters for the above data types.

The reason you're getting no current record is because FindFirst is not returning
anything. You should use .NoMatch to check for results.
If rs.NoMatch Then
........


--
HTH
Dan Artuso, Access MVP


Dickie Black said:
Thanks,

I've just tried rs!Title, but get an error message: "No current record" Is
there a line needed after the find first statement ?

Also, the date variables have the # character inserted into them earlier in
the code so that shouldn't be a problem


Dan Artuso said:
Hi,
You have to delimit dates with #
WHERE ((([Session].[Date]) Between #" &
DateMon & "# AND #" & DateFri & "#));"

Then to refer to a field in the rs use:
rs!NameOfField

Date is also a bad choice for a field name as it's also a function.


--
HTH
Dan Artuso, Access MVP


Hi,

I'd like to set the caption for a group of labels on an unbound form
depending on the presence of a record in a table. That is, if a record
exists for Monday 12th July at 9:30 AM I would like for a box to show one of
the fields from that record.

I have tried the following:

'create recordset for week described in week commencing combo
Dim rs As DAO.Recordset
Dim SQLString As String

SQLString = "SELECT * FROM [Session] WHERE ((([Session].[Date]) Between " &
DateMon & " AND " & DateFri & "));"
Set rs = CurrentDb.OpenRecordset(SQLString)

'set values for boxes to relevant record
rs.FindFirst ("[Date] = DateMon And [StartTime] = Session1")
Me.Session_1_1.Caption = [Title]

'close recordset
Set rs = Nothing

where the values for Session1, DateMon and DateFri are set earlier in the
code.

I know the line Me.Session_1_1.Caption = [Title] does not work, but think it
gives an impression of what I'm trying to achieve. I want to find a record
that occurs at the time and date specified then set the caption of the label
to the value of the [Title] field. Am I going about this correctly?

TIA,

Dickie Black
Click to expand...
Click to expand...
Click to expand...
 
Back
Top