Help please on Record sets

G

Guest

I am Trying to create a recordset from an SQL query and cycle through the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why. This is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit working'

strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
 
D

Douglas J. Steele

You're missing spaces in the SQL statement: either put a space before the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host Data]![HostId] &
"'"
 
G

Guest

Many thanks Doug for your prompt reply, I tried with the spaces and it didn't
work. I must admit I wasn't worried about the Sql string, it was the rest of
the connection and recordset handling that I wasn't sure on. I must be
truthful and admit that the SqlString was a little more complex and involved
dates which I edited out to make the question simple. This is the real SQL
string:-
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] + 13 & "#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host Data]![Da1] & "#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and can you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
Douglas J. Steele said:
You're missing spaces in the SQL statement: either put a space before the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host Data]![HostId] &
"'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
I am Trying to create a recordset from an SQL query and cycle through the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why. This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit working'

strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What's your Short Date format set to (in Regional Settings)? If it's
dd/mm/yyyy, you'll definitely have problems with your SQL.

I'd recommend

And AccomodationDates.StartDate <= " & _
Format(DateAdd("d", 13, [Forms]![Host Data]![Da1]), "\#mm\/dd\/yyyy\#) _
& " And AccomodationDates.EndDate >= _
Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#")

Try printing strSQL out to the Debug window and take a look at it. Does it
look right? Does it run when you copy that SQL into the Query Window's SQL
View?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Many thanks Doug for your prompt reply, I tried with the spaces and it
didn't
work. I must admit I wasn't worried about the Sql string, it was the rest
of
the connection and recordset handling that I wasn't sure on. I must be
truthful and admit that the SqlString was a little more complex and
involved
dates which I edited out to make the question simple. This is the real SQL
string:-
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] + 13 &
"#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host Data]![Da1] &
"#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and can you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
Douglas J. Steele said:
You're missing spaces in the SQL statement: either put a space before the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host Data]![HostId]
&
"'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
I am Trying to create a recordset from an SQL query and cycle through
the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why.
This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit
working'

strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug Many Thanks.
I had to add a quote to your first Formated Date and Change the quotes to
single quotes before my compiler would accept them like this:

Format(DateAdd('d', 13, [Forms]![Host Data]![Da1]), '\#mm\/dd\/yyyy\#')

but needless to say it still crashed Access.
My regional settings are dd\mm\yy by the way.
As my original SQL string was generated in a working query, would it be
easier to leave out strSql and make a reference back to that to collect my
recordset or would that lead to more problems?
Andy...
****************************

Douglas J. Steele said:
What's your Short Date format set to (in Regional Settings)? If it's
dd/mm/yyyy, you'll definitely have problems with your SQL.

I'd recommend

And AccomodationDates.StartDate <= " & _
Format(DateAdd("d", 13, [Forms]![Host Data]![Da1]), "\#mm\/dd\/yyyy\#) _
& " And AccomodationDates.EndDate >= _
Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#")

Try printing strSQL out to the Debug window and take a look at it. Does it
look right? Does it run when you copy that SQL into the Query Window's SQL
View?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Many thanks Doug for your prompt reply, I tried with the spaces and it
didn't
work. I must admit I wasn't worried about the Sql string, it was the rest
of
the connection and recordset handling that I wasn't sure on. I must be
truthful and admit that the SqlString was a little more complex and
involved
dates which I edited out to make the question simple. This is the real SQL
string:-
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] + 13 &
"#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host Data]![Da1] &
"#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and can you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
Douglas J. Steele said:
You're missing spaces in the SQL statement: either put a space before the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host Data]![HostId]
&
"'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am Trying to create a recordset from an SQL query and cycle through
the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why.
This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit
working'

strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry: You're correct that you needed single quotes (or you could have
doubled up the double quotes in the Format statement).

You haven't answered my question about strSQL, though. Did you print it out
to the Debug window and look at it? Did you try running it in the query
builder? Just because your "original SQL string was generated in a working
query" doesn't actually guarantee that you're building it correcting in your
VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Doug Many Thanks.
I had to add a quote to your first Formated Date and Change the quotes to
single quotes before my compiler would accept them like this:

Format(DateAdd('d', 13, [Forms]![Host Data]![Da1]), '\#mm\/dd\/yyyy\#')

but needless to say it still crashed Access.
My regional settings are dd\mm\yy by the way.
As my original SQL string was generated in a working query, would it be
easier to leave out strSql and make a reference back to that to collect my
recordset or would that lead to more problems?
Andy...
****************************

Douglas J. Steele said:
What's your Short Date format set to (in Regional Settings)? If it's
dd/mm/yyyy, you'll definitely have problems with your SQL.

I'd recommend

And AccomodationDates.StartDate <= " & _
Format(DateAdd("d", 13, [Forms]![Host Data]![Da1]),
"\#mm\/dd\/yyyy\#) _
& " And AccomodationDates.EndDate >= _
Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#")

Try printing strSQL out to the Debug window and take a look at it. Does
it
look right? Does it run when you copy that SQL into the Query Window's
SQL
View?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Many thanks Doug for your prompt reply, I tried with the spaces and it
didn't
work. I must admit I wasn't worried about the Sql string, it was the
rest
of
the connection and recordset handling that I wasn't sure on. I must be
truthful and admit that the SqlString was a little more complex and
involved
dates which I edited out to make the question simple. This is the real
SQL
string:-
strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host
Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] + 13
&
"#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host Data]![Da1]
&
"#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and can
you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
:

You're missing spaces in the SQL statement: either put a space before
the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host
Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host
Data]![HostId]
&
"'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am Trying to create a recordset from an SQL query and cycle through
the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why.
This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit
working'

strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but
I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I tried running it from the query builder after deleting all the
concatenations and the (&) you sugested before the HostId clause, (it didn't
like that at all) but after it had made a successful query out of it and
accepted it, It said that the calculation was too complex and came up with no
resulting table. I copied it into the immediate window to examine it and all
looked ok but then 'some times you can't see the wood for the trees' as we
say.
Question: which would be better? To filter all the unwanted records within
the SQL string leaving a small recordset to work with but a complex string or
just to 'SELECT* from' and then deal with unwated data in the following code?
My thanks for your patience with me.
Andy...
****************************
Douglas J. Steele said:
Sorry: You're correct that you needed single quotes (or you could have
doubled up the double quotes in the Format statement).

You haven't answered my question about strSQL, though. Did you print it out
to the Debug window and look at it? Did you try running it in the query
builder? Just because your "original SQL string was generated in a working
query" doesn't actually guarantee that you're building it correcting in your
VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Doug Many Thanks.
I had to add a quote to your first Formated Date and Change the quotes to
single quotes before my compiler would accept them like this:

Format(DateAdd('d', 13, [Forms]![Host Data]![Da1]), '\#mm\/dd\/yyyy\#')

but needless to say it still crashed Access.
My regional settings are dd\mm\yy by the way.
As my original SQL string was generated in a working query, would it be
easier to leave out strSql and make a reference back to that to collect my
recordset or would that lead to more problems?
Andy...
****************************

Douglas J. Steele said:
What's your Short Date format set to (in Regional Settings)? If it's
dd/mm/yyyy, you'll definitely have problems with your SQL.

I'd recommend

And AccomodationDates.StartDate <= " & _
Format(DateAdd("d", 13, [Forms]![Host Data]![Da1]),
"\#mm\/dd\/yyyy\#) _
& " And AccomodationDates.EndDate >= _
Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#")

Try printing strSQL out to the Debug window and take a look at it. Does
it
look right? Does it run when you copy that SQL into the Query Window's
SQL
View?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Many thanks Doug for your prompt reply, I tried with the spaces and it
didn't
work. I must admit I wasn't worried about the Sql string, it was the
rest
of
the connection and recordset handling that I wasn't sure on. I must be
truthful and admit that the SqlString was a little more complex and
involved
dates which I edited out to make the question simple. This is the real
SQL
string:-
strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host
Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] + 13
&
"#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host Data]![Da1]
&
"#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and can
you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
:

You're missing spaces in the SQL statement: either put a space before
the
closing quote on each line, or start each line with a space after the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host
Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host
Data]![HostId]
&
"'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am Trying to create a recordset from an SQL query and cycle through
the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see Why.
This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit
working'

strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form but
I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Ferguson

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF
Click to expand...

I think these lines could make the computer pretty busy for a long time...

Should there be something inside this loop?

All the best


Tim F
 
D

Douglas J. Steele

You misunderstood.

I meant after you've assigned the SQL statement to strSQL, put

Debug.Print strSQL

in your code.

That will write the resultant string to the Debug window. Go to the Debug
window (Ctrl-G), copy the string there and paste it into the SQL View in the
query builder.

However, Tim's absolutely correct that

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

doesn't make any sense.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
I tried running it from the query builder after deleting all the
concatenations and the (&) you sugested before the HostId clause, (it
didn't
like that at all) but after it had made a successful query out of it and
accepted it, It said that the calculation was too complex and came up with
no
resulting table. I copied it into the immediate window to examine it and
all
looked ok but then 'some times you can't see the wood for the trees' as we
say.
Question: which would be better? To filter all the unwanted records within
the SQL string leaving a small recordset to work with but a complex string
or
just to 'SELECT* from' and then deal with unwated data in the following
code?
My thanks for your patience with me.
Andy...
****************************
Douglas J. Steele said:
Sorry: You're correct that you needed single quotes (or you could have
doubled up the double quotes in the Format statement).

You haven't answered my question about strSQL, though. Did you print it
out
to the Debug window and look at it? Did you try running it in the query
builder? Just because your "original SQL string was generated in a
working
query" doesn't actually guarantee that you're building it correcting in
your
VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Doug Many Thanks.
I had to add a quote to your first Formated Date and Change the quotes
to
single quotes before my compiler would accept them like this:

Format(DateAdd('d', 13, [Forms]![Host Data]![Da1]), '\#mm\/dd\/yyyy\#')

but needless to say it still crashed Access.
My regional settings are dd\mm\yy by the way.
As my original SQL string was generated in a working query, would it be
easier to leave out strSql and make a reference back to that to collect
my
recordset or would that lead to more problems?
Andy...
****************************

:

What's your Short Date format set to (in Regional Settings)? If it's
dd/mm/yyyy, you'll definitely have problems with your SQL.

I'd recommend

And AccomodationDates.StartDate <= " & _
Format(DateAdd("d", 13, [Forms]![Host Data]![Da1]),
"\#mm\/dd\/yyyy\#) _
& " And AccomodationDates.EndDate >= _
Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#")

Try printing strSQL out to the Debug window and take a look at it.
Does
it
look right? Does it run when you copy that SQL into the Query Window's
SQL
View?


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Many thanks Doug for your prompt reply, I tried with the spaces and
it
didn't
work. I must admit I wasn't worried about the Sql string, it was the
rest
of
the connection and recordset handling that I wasn't sure on. I must
be
truthful and admit that the SqlString was a little more complex and
involved
dates which I edited out to make the question simple. This is the
real
SQL
string:-
strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON
[Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host
Data]![HostId])
And AccomodationDates.StartDate <= #" & [Forms]![Host Data]![Da1] +
13
&
"#" _
& " And AccomodationDates.EndDate >= #" & [Forms]![Host
Data]![Da1]
&
"#;"
(This editor realy screws it up doesn't it)
does it look correct to you or is my handleing of dates wrong? and
can
you
confirm that the Connection and Record set parts look ok.
Again Many Many thanks
Andy..
***********************************
:

You're missing spaces in the SQL statement: either put a space
before
the
closing quote on each line, or start each line with a space after
the
opening quote.

As well, you're missing a closing quote in the last line of the SQL
statement.

If FamilyID is numeric, I'd recommend

& "WHERE AccomodationDates.FamilyID = " & [Forms]![Host
Data]![HostId]

If it's text, then

& "WHERE AccomodationDates.FamilyID = '" & [Forms]![Host
Data]![HostId]
&
"'"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I am Trying to create a recordset from an SQL query and cycle
through
the
records and use the data collected in a form.
the code I have created so far crashes Access and I cannot see
Why.
This
is
what I have so far:

Private Function Dateline()
On Error Resume Next

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSql As String
Dim i%, h%, j%, r% 'to be used later when I've got this bit
working'

strSql = "SELECT [Students Data].LastName,
AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& "FROM [Students Data] INNER JOIN AccomodationDates ON
[Students
Data].StudentID = AccomodationDates.StudentId" _
& "WHERE (((AccomodationDates.FamilyID) = [Forms]![Host
Data]![HostId]);

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & CurrentProject.Path &
"\St_Georges.mdb"
.CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open strSql, cn

If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'
Loop

End If

End Function

I Know that the SQL returns the correct data, I've tested that.
It's how to work with the data in the record set.
This is a long way round for puting such simple data on a form
but
I'm
trying to self teach RecordSets and VBA.
Many Thanks in advance for any help.
Andy.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Tim.
It actualy reads:-

If rs.RecordCount <> 0 Then
rs.MoveFirst

Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

Loop

End If

Do you think this is wrong??

Many Thanks.
Andy
*****************************
 
J

John Spencer

That is an infinite loop. It will run forever unless you add something like
rs.MoveNext inside the loop to get it to move through the recordset.



Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

rs.MoveNext '<<<<<--------

Loop

End If
Hi Tim.
It actualy reads:-

If rs.RecordCount <> 0 Then
rs.MoveFirst

Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

Loop

End If

Do you think this is wrong??

Many Thanks.
Andy
*****************************
Tim Ferguson said:
I think these lines could make the computer pretty busy for a long time...

Should there be something inside this loop?

All the best


Tim F
Click to expand...
Click to expand...
 
G

Guest

I had tried that Doug, I'm sorry I don't always explain properly all the
things I'm trying here but I do Listen to every bodys sugestions and attempt
to implement them.
The resultant SQL from the Immediate window says it is too complex to
calculate or has been written incorrectly.

Andy.
**************
 
G

Guest

Thank you John.
Have inserted 'rs.MoveNext' inside the loop but Access still crashes out. I
think I have problem with strSql and the rs loop and they are playing against
each other. The string now reads: (after some doctoring by the venerable and
much apprecieated Doug)
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host Data]![HostId])
And AccomodationDates.StartDate <=Format(DateAdd('d', 13, [Forms]![Host
Data]![Da1]),'\#mm\/dd\/yyyy\#')" _
& " And AccomodationDates.EndDate >= Format([Forms]![Host Data]![Da1],
'\#mm\/dd\/yyyy\#');"

Many Thanks
Andy
********************

John Spencer said:
That is an infinite loop. It will run forever unless you add something like
rs.MoveNext inside the loop to get it to move through the recordset.



Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

rs.MoveNext '<<<<<--------

Loop

End If
Hi Tim.
It actualy reads:-

If rs.RecordCount <> 0 Then
rs.MoveFirst

Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

Loop

End If

Do you think this is wrong??

Many Thanks.
Andy
*****************************
Tim Ferguson said:
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

I think these lines could make the computer pretty busy for a long time...

Should there be something inside this loop?

All the best


Tim F
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If you can't run the SQL you're generating in the Query window, how do you
expect to run it through VBA?

What is the SQL you generated? (Copy and paste it into your reply)
 
D

Douglas J. Steele

I see now where the confusion about single and double quotes came from.

I'd suggested that the Format statements be outside of the string:

& " WHERE (AccomodationDates.FamilyID = " & [Forms]![Host Data]![HostId]) _
& " And AccomodationDates.StartDate <= " _
& Format(DateAdd('d', 13, [Forms]![Host Data]![Da1]),"\#mm\/dd\/yyyy\#") _
& " And AccomodationDates.EndDate >= " _
& Format([Forms]![Host Data]![Da1], "\#mm\/dd\/yyyy\#"

That's why the SQL you're generating is being flagged as "is too complex to
calculate or has been written incorrectly."

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
Thank you John.
Have inserted 'rs.MoveNext' inside the loop but Access still crashes out.
I
think I have problem with strSql and the rs loop and they are playing
against
each other. The string now reads: (after some doctoring by the venerable
and
much apprecieated Doug)
strSql = "SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo" _
& " FROM [Students Data] INNER JOIN AccomodationDates ON [Students
Data].StudentID = AccomodationDates.StudentId" _
& " WHERE (AccomodationDates.FamilyID =&[Forms]![Host Data]![HostId])
And AccomodationDates.StartDate <=Format(DateAdd('d', 13, [Forms]![Host
Data]![Da1]),'\#mm\/dd\/yyyy\#')" _
& " And AccomodationDates.EndDate >= Format([Forms]![Host Data]![Da1],
'\#mm\/dd\/yyyy\#');"

Many Thanks
Andy
********************

John Spencer said:
That is an infinite loop. It will run forever unless you add something
like
rs.MoveNext inside the loop to get it to move through the recordset.



Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

rs.MoveNext '<<<<<--------

Loop

End If
Hi Tim.
It actualy reads:-

If rs.RecordCount <> 0 Then
rs.MoveFirst

Do Until rs.EOF

Me![Date1].Value = StartDate 'from the record set'
Me![Student].Value = LastName 'again from the record set'

Loop

End If

Do you think this is wrong??

Many Thanks.
Andy
*****************************
:


If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF

I think these lines could make the computer pretty busy for a long
time...

Should there be something inside this loop?

All the best


Tim F
Click to expand...
Click to expand...
Click to expand...
 
T

Tim Ferguson

Have inserted 'rs.MoveNext' inside the loop but Access still crashes out.
Click to expand...

Depends where you put it... and whether the .MoveFirst is still there. What
does the loop look like now?

Tim F
 
G

Guest

The SQL runs fine now Doug after a bit of tinkering with the date formats and
the occassional missed space, I'm now thinking of changeing the rest of the
code for this Function to DOA instead of ADO, I'll let you know if I get any
problems and may I say many thanks to you and other helpers for your
sugestions. The SQL now reads:-

SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo FROM [Students Data] INNER JOIN AccomodationDates ON
[Students Data].StudentID = AccomodationDates.StudentId WHERE
(AccomodationDates.FamilyID = [Forms]![Host Data]![HostId]) And
Format(AccomodationDates.StartDate, 'mm/dd/yy') <= Format(DateAdd('d', 13,
[Forms]![Host Data]![Da1]), 'mm/dd/yy') And Format(AccomodationDates.EndDate,
'mm/dd/yy') >= Format([Forms]![Host Data]![Da1], 'mm/dd/yy');

Onwards and Upwards. Thanks.
Andy...
********************************
 
D

Douglas J. Steele

I would strongly recommend changing how you're comparing the dates!

Comparing strings in mm/dd/yy format is not generally very effective.

AccomodationDates.StartDate <= Format(DateAdd('d', 13, [Forms]![Host
Data]![Da1]), '\#mm\/dd\/yyyy\#') And AccomodationDates.EndDate >=
Format([Forms]![Host Data]![Da1], '\#mm\/dd\/yyyy\#');


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Andy6 said:
The SQL runs fine now Doug after a bit of tinkering with the date formats
and
the occassional missed space, I'm now thinking of changeing the rest of
the
code for this Function to DOA instead of ADO, I'll let you know if I get
any
problems and may I say many thanks to you and other helpers for your
sugestions. The SQL now reads:-

SELECT [Students Data].LastName, AccomodationDates.FamilyID,
AccomodationDates.StartDate, AccomodationDates.EndDate,
AccomodationDates.BedNo FROM [Students Data] INNER JOIN AccomodationDates
ON
[Students Data].StudentID = AccomodationDates.StudentId WHERE
(AccomodationDates.FamilyID = [Forms]![Host Data]![HostId]) And
Format(AccomodationDates.StartDate, 'mm/dd/yy') <= Format(DateAdd('d', 13,
[Forms]![Host Data]![Da1]), 'mm/dd/yy') And
Format(AccomodationDates.EndDate,
'mm/dd/yy') >= Format([Forms]![Host Data]![Da1], 'mm/dd/yy');

Onwards and Upwards. Thanks.
Andy...
********************************


Douglas J. Steele said:
If you can't run the SQL you're generating in the Query window, how do
you
expect to run it through VBA?

What is the SQL you generated? (Copy and paste it into your reply)
Click to expand...
Click to expand...
 
T

Tim Ferguson

=?Utf-8?B?VG9ueV9WQkFDb2Rlcg==?=
I believe it does not matter if you
omit the rs.MoveFirst statement, as it will always start looping from
the beginning of the recordset.
Click to expand...

Jolly well does matter if the .MoveFirst is still inside the loop: it won't
_start_ looping from the beginning of the recordset; it'll _stay_ there
forever!

All the best


Tim F
 

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

Can someone please correct my codes ? 3
adOpenStatic Recordset still changing my table 5
ADO Access For Value List Error 4
EXPORT ACCESS TO SQL DATABASE RECORD BY RECORD... 1
ADODB HELP 7
Exporting Access Data into Email Body 1
Error 3029 When Trying to Create Workspace 1
ADO - Retrieve record with InStr criteria 4

Top