select sql dynamic query problem

[Philip_collins]

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

[CDMAPoster]

On Aug 28, 9:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

If you open up a form immediately after doing this then perhaps Access
is going through either the parsing phase or the optimization phase on
the new querydef and causing a timing issue. I believe that such
operations run asynchronously. That's where I'd look first. It also
explains why it works in one database and not in another.

James A. Fortune
CD********@FortuneJames.com

[Philip_collins]

On 28 Aug, 20:13, [e-mail] wrote:

On Aug 28, 9:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

If you open up a form immediately after doing this then perhaps Access
is going through either the parsing phase or the optimization phase on
the new querydef and causing a timing issue. I believe that such
operations run asynchronously. That's where I'd look first. It also
explains why it works in one database and not in another.

James A. Fortune
[e-mail]- Hide quoted text -

- Show quoted text -

Thanks for the reply!
end of previous db code.
Set qdf = dbs.CreateQueryDef("qryCallHistory", strsql)

DoCmd.OpenForm "frmcallhistory"
Set dbs = Nothing
error handling. etc

on the current code I'm using another event??
at the risk of repeating myself. Baffled! I have spent several
fruitless hours searching help files of vaious descriptions.

[Ben]

On Aug 28, 6:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

Make sure that your "zz" is a number format and that the
registrationno is also a number format, otherwise it will need more
quotes.

[Philip_collins]

On 28 Aug, 21:44, Ben <[e-mail]:

On Aug 28, 6:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

Make sure that your "zz" is a number format and that the
registrationno is also a number format, otherwise it will need more
quotes.- Hide quoted text -

- Show quoted text -

do you declare it as an
integer?

[CDMAPoster]

On Aug 28, 4:54 pm, [e-mail] wrote:

...
Thanks again for your interest end of currenent db code
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing
error handling etc
i then use the qury later?
this is what baffles me!
Thanks very much for your interest.
Regards
Phil

By programming in a delay, the idea is that an adequate delay gives
Access time to do any housekeeping on the QueryDef so that query is
ready for the form to use as its RecordSource. If the change I
recommend doesn't solve the problem try a DoEvents loop. If that
doesn't solve the problem then it's quite unlikely that timing is the
cause of the problem and you need to look elsewhere. Usually a
parameter prompt is a result of a misspelled field name, but if the
code is the same as that in the database that works, it rules that
out. Maybe you put 'restration' in instead of 'registration.' If it
turns out that the zz is not the problem, as suggested by Ben, you can
also print the query string out to a file before copying and pasting
the text into the Query By Example (QBE) window.

James A. Fortune
CD********@FortuneJames.com

[Philip_collins]

On 28 Aug, 21:59, [e-mail] wrote:

On 28 Aug, 21:44, Ben <[e-mail]:

On Aug 28, 6:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

Make sure that your "zz" is a number format and that the
registrationno is also a number format, otherwise it will need more
quotes.- Hide quoted text -

- Show quoted text -

do you declare it as an
integer?- Hide quoted text -

- Show quoted text -

Sorry to be dense.you mean the strsql?
Thanks
Phil.

[Philip_collins]

On 28 Aug, 21:44, Ben <[e-mail]:

On Aug 28, 6:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

Make sure that your "zz" is a number format and that the
registrationno is also a number format, otherwise it will need more
quotes.- Hide quoted text -

- Show quoted text -

It cant be in a number format as its vehicle registrations. when you
sy more quotes where exactly?
regards
Phil

[Philip_collins]

On 28 Aug, 22:22, [e-mail] wrote:

On 28 Aug, 21:59, [e-mail] wrote:

On 28 Aug, 21:44, Ben <[e-mail]:

On Aug 28, 6:17 am, [e-mail] wrote:

Hi!
the following sql is identical to code in another database of mine. in
the first database it works perfectly, that is to say it writes the
query and I have a form based on the query. in the current database
the same sql when u base a form or report on it or indeed simply open
it, comes up with a box with the restration on it asking for a
perameter if you put the same registration no it works ok. baffled!

Dim rec As Recordset
Dim qdf As QueryDef
Dim strsql

Set db = CurrentDb
db.QueryDefs.Refresh
For Each qdf In db.QueryDefs
If qdf.Name = "qryWeightsTotals" Then
db.QueryDefs.Delete qdf.Name
End If
Next qdf

strsql = "SELECT [Date],[makemodel],pao, Street,[town],
[Postcode],[gross weight],tare,[net(kg)] FROM tblVanDetails WHERE
[RegistrationNo] = " & zz & ";"

End If
Set qdf = db.CreateQueryDef("qryWeightsTotals",
strsql) '
db.QueryDefs.Refresh
Me.Refresh
Set db = Nothing

I'm also trying to get it to total on the [net(kg)] field but that is
of secondary importance.
any help would be much appreciated.
Thanks in advance
Phil

Make sure that your "zz" is a number format and that the
registrationno is also a number format, otherwise it will need more
quotes.- Hide quoted text -

- Show quoted text -

do you declare it as an
integer?- Hide quoted text -

- Show quoted text -

Sorry to be dense.you mean the strsql?
Thanks
Phil.- Hide quoted text -

- Show quoted text -

thank you both very much for your patient help. ben gave me a clue
when he spoke about more quotes, works fine now thans again!
regards
Phil