S
Simeon Cheeseman
hi there.
I have been attempting to fix this for just over a week, the problems result
in either the queries not returning anything or the movenext function not
working, please could you help me, any ideas would be helpful.
The purpose of this code is to populate a bookings review form with the
other people staying at the same time as the one being booked in, this has
proved difficult.
thanks simeon.
Private Sub Form_Open(Cancel As Integer)
Dim id_str As String
Dim qry As String
Dim qry2 As String
Dim qry3 As String
Dim cf1 As String
Dim date_id1 As Integer
Dim date_id2 As Integer
Dim date_id3 As Integer
Dim rst As Recordset
Dim db As Database
Dim count As Integer
Dim enddate As String
Dim startdate As String
Me![txtfirst_name] = [Form_Bookings Form 1]![firstname]
Me!txtlast_name = [Form_Bookings Form 1]![surname]
Me![txtstart_date] = [Form_Bookings Form 1]![date1]
Me![txtend_date] = DateValue([Form_Bookings Form 1]![date1]) +
Val([Form_Bookings Form 1]![date2])
startdate = Format(Me!txtstart_date, "\#mm\/dd\/yyyy\#")
enddate = Format(Me!txtend_date, "\#mm\/dd\/yyyy\#")
id_str = [Form_Bookings Form 1].txt_id
Set db = CodeDb()
Set rst = db.OpenRecordset("SELECT [child_id], house, date_booked_start,
date_booked_end " & _
"FROM tbl_bookings " & _
"WHERE (((date_booked_start) Between " &
startdate & " And " & enddate & ") " & _
"OR ((date_booked_end) Between " & startdate
& " And " & enddate & "));")
date_id1 = 0
date_id2 = 0
date_id3 = 0
cf1 = house ' this is a check to see if the query is working
If Not rst.EOF Then
rst.MoveFirst 'set recordset to first record
count = 0
While Not rst.EOF And count <= 3
count = count + 1
If count = 1 Then
date_id1 = child_id
rst.MoveNext
Else
If count = 2 Then
date_id2 = child_id
rst.MoveNext
Else
If count = 3 Then
date_id3 = child_id
rst.MoveNext
End If
End If
End If
Wend
If date_id1 <> 0 Then
qry3 = "SELECT child_id, first_name, surname FROM tbl_details "
& _
"WHERE (((child_id) = " & date_id1 & "))"
Me.RecordSource = qry3
Me.Requery
Me!txtochildf1.Value = first_name
Me!txtochilds1.Value = surname
End If
If date_id2 <> 0 Then
qry3 = "SELECT child_id, first_name, surname FROM tbl_details "
& _
"WHERE (((child_id) = " & date_id2 & "))"
Me.RecordSource = qry3
Me.Requery
Me!txtochildf2.Value = first_name
Me!txtochilds2.Value = surname
End If
If date_id3 <> 0 Then
qry3 = "SELECT child_id, first_name, surname FROM tbl_details "
& _
"WHERE (((child_id) = " & date_id3 & "))"
Me.RecordSource = qry3
Me.Requery
Me!txtochildf3.Value = first_name
Me!txtochilds3.Value = surname
End If
End If
qry = "SELECT [allocated_days_left] FROM tbl_days_allocated " & _
"WHERE [child_id] = " & id_str
Me.RecordSource = qry ' sets the source of the form to qry
Me.Requery
Me![txtallocated_days_left].Value = ([allocated_days_left] -
[Form_Bookings Form 1]![date2])
Me.RecordSource = "" 'blanks out the record source
End Sub
I have been attempting to fix this for just over a week, the problems result
in either the queries not returning anything or the movenext function not
working, please could you help me, any ideas would be helpful.
The purpose of this code is to populate a bookings review form with the
other people staying at the same time as the one being booked in, this has
proved difficult.
thanks simeon.
Private Sub Form_Open(Cancel As Integer)
Dim id_str As String
Dim qry As String
Dim qry2 As String
Dim qry3 As String
Dim cf1 As String
Dim date_id1 As Integer
Dim date_id2 As Integer
Dim date_id3 As Integer
Dim rst As Recordset
Dim db As Database
Dim count As Integer
Dim enddate As String
Dim startdate As String
Me![txtfirst_name] = [Form_Bookings Form 1]![firstname]
Me!txtlast_name = [Form_Bookings Form 1]![surname]
Me![txtstart_date] = [Form_Bookings Form 1]![date1]
Me![txtend_date] = DateValue([Form_Bookings Form 1]![date1]) +
Val([Form_Bookings Form 1]![date2])
startdate = Format(Me!txtstart_date, "\#mm\/dd\/yyyy\#")
enddate = Format(Me!txtend_date, "\#mm\/dd\/yyyy\#")
id_str = [Form_Bookings Form 1].txt_id
Set db = CodeDb()
Set rst = db.OpenRecordset("SELECT [child_id], house, date_booked_start,
date_booked_end " & _
"FROM tbl_bookings " & _
"WHERE (((date_booked_start) Between " &
startdate & " And " & enddate & ") " & _
"OR ((date_booked_end) Between " & startdate
& " And " & enddate & "));")
date_id1 = 0
date_id2 = 0
date_id3 = 0
cf1 = house ' this is a check to see if the query is working
If Not rst.EOF Then
rst.MoveFirst 'set recordset to first record
count = 0
While Not rst.EOF And count <= 3
count = count + 1
If count = 1 Then
date_id1 = child_id
rst.MoveNext
Else
If count = 2 Then
date_id2 = child_id
rst.MoveNext
Else
If count = 3 Then
date_id3 = child_id
rst.MoveNext
End If
End If
End If
Wend
If date_id1 <> 0 Then
qry3 = "SELECT child_id, first_name, surname FROM tbl_details "
& _
"WHERE (((child_id) = " & date_id1 & "))"
Me.RecordSource = qry3
Me.Requery
Me!txtochildf1.Value = first_name
Me!txtochilds1.Value = surname
End If
If date_id2 <> 0 Then
qry3 = "SELECT child_id, first_name, surname FROM tbl_details "
& _
"WHERE (((child_id) = " & date_id2 & "))"
Me.RecordSource = qry3
Me.Requery
Me!txtochildf2.Value = first_name
Me!txtochilds2.Value = surname
End If
If date_id3 <> 0 Then
qry3 = "SELECT child_id, first_name, surname FROM tbl_details "
& _
"WHERE (((child_id) = " & date_id3 & "))"
Me.RecordSource = qry3
Me.Requery
Me!txtochildf3.Value = first_name
Me!txtochilds3.Value = surname
End If
End If
qry = "SELECT [allocated_days_left] FROM tbl_days_allocated " & _
"WHERE [child_id] = " & id_str
Me.RecordSource = qry ' sets the source of the form to qry
Me.Requery
Me![txtallocated_days_left].Value = ([allocated_days_left] -
[Form_Bookings Form 1]![date2])
Me.RecordSource = "" 'blanks out the record source
End Sub