Where to put SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am in a form and after selecting a value for one field, under the On Click
property, want to run a query to choose a value to set the Default Value of
another field. I am thinking this is SQL language but am not sure if it goes
in Code or Macro or Expression. When I use Code and put SQL language in the
Visual Basic there, I get errors.

Any idea? Thanks for help!

MIKE
 
Hi,


If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = CurrentDb.OpenRecordset("SELECT f1 FROM t1 WHERE
NumericalField=" & _
Me.ControlWIthNumericalValue ).Fields(0).Value
End If



Note: that would create an error if the query does not return any record.
You may use a DLookup( ) if all you need is to read a single table:

If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = DLookup("f1", "t1", "NumericalField=" &
Me.ControlWIthNumericalValue )
End If



should do the same as the previous solution, but wihtout error if there is
no match (a NULL will be returned). The second argument of DLookup limits
you to a single table, or to a saved query.



Hoping it may help,
Vanderghast, Access MVP
 
To run a query on an event, you must use a macro or VBA code that actually
runs the query. Create a macro to run the query, or create code in the
form's module to run the query. No SQL string goes into the property box for
the event.
 
The code I am trying is:

Dim MyDB As Database
Set MyDB = CurrentDb()
[Begin Number].DefaultValue = MyDB.OpenRecordset
("SELECT Max(IIf([Check Log].[Date]=[Check Log Usage Most Recent
Dates].[Recent Date],
[End Number],0))+1 FROM [Check Log] LEFT JOIN [Check Log Usage Most Recent
Dates] ON
[Check Log].[Check Use] = [Check Log Usage Most Recent Dates].Usage
GROUP BY [Check Log].[Check Use]
HAVING ((([Check Log].[Check Use])=[Forms]![Check Log]![Check Use]))")

This is giving me errors. [Check Log] is a table name, [Check Log Usage
Most Recent Dates] is a query name.

Am I missing something? Thanks
MIKE
 
Hi,


The whole. long, syntax, would be:




Dim rst As DAO.Recordset
Set rst=CurrentDb.OpenRecordset( SQLstring )
ObjectName.Property = rst.Fields(0).Value
Set rst=Nothing


we make it shorter (one line of code) with:


ObjectName.Property = CurrentDb.OpenRecordset(
SQLstring).Fields(0).Value


Your code miss the ending: .Fields(0).Value


Note: you can make further enhancement as specifying you open the recordset
for read only, and forward only (no backward mechanic-pointers to be
initialized), so the execution can be even faster, but that is optimization.

Hoping it may help,
Vanderghast, Access MVP



Mike Binger said:
The code I am trying is:

Dim MyDB As Database
Set MyDB = CurrentDb()
[Begin Number].DefaultValue = MyDB.OpenRecordset
("SELECT Max(IIf([Check Log].[Date]=[Check Log Usage Most Recent
Dates].[Recent Date],
[End Number],0))+1 FROM [Check Log] LEFT JOIN [Check Log Usage Most Recent
Dates] ON
[Check Log].[Check Use] = [Check Log Usage Most Recent Dates].Usage
GROUP BY [Check Log].[Check Use]
HAVING ((([Check Log].[Check Use])=[Forms]![Check Log]![Check Use]))")

This is giving me errors. [Check Log] is a table name, [Check Log Usage
Most Recent Dates] is a query name.

Am I missing something? Thanks
MIKE

Michel Walsh said:
Hi,


If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = CurrentDb.OpenRecordset("SELECT f1 FROM t1 WHERE
NumericalField=" & _
Me.ControlWIthNumericalValue ).Fields(0).Value
End If



Note: that would create an error if the query does not return any record.
You may use a DLookup( ) if all you need is to read a single table:

If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = DLookup("f1", "t1", "NumericalField=" &
Me.ControlWIthNumericalValue )
End If



should do the same as the previous solution, but wihtout error if there
is
no match (a NULL will be returned). The second argument of DLookup limits
you to a single table, or to a saved query.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
I am using that and the syntax still isn't right. The query is also set up
as an actual query, but when I try the OpenRecordset with that query name in
quotes I get the run-time error '3061': Too few parameters. Expected 1.

MIKE

Michel Walsh said:
Hi,


The whole. long, syntax, would be:




Dim rst As DAO.Recordset
Set rst=CurrentDb.OpenRecordset( SQLstring )
ObjectName.Property = rst.Fields(0).Value
Set rst=Nothing


we make it shorter (one line of code) with:


ObjectName.Property = CurrentDb.OpenRecordset(
SQLstring).Fields(0).Value


Your code miss the ending: .Fields(0).Value


Note: you can make further enhancement as specifying you open the recordset
for read only, and forward only (no backward mechanic-pointers to be
initialized), so the execution can be even faster, but that is optimization.

Hoping it may help,
Vanderghast, Access MVP



Mike Binger said:
The code I am trying is:

Dim MyDB As Database
Set MyDB = CurrentDb()
[Begin Number].DefaultValue = MyDB.OpenRecordset
("SELECT Max(IIf([Check Log].[Date]=[Check Log Usage Most Recent
Dates].[Recent Date],
[End Number],0))+1 FROM [Check Log] LEFT JOIN [Check Log Usage Most Recent
Dates] ON
[Check Log].[Check Use] = [Check Log Usage Most Recent Dates].Usage
GROUP BY [Check Log].[Check Use]
HAVING ((([Check Log].[Check Use])=[Forms]![Check Log]![Check Use]))")

This is giving me errors. [Check Log] is a table name, [Check Log Usage
Most Recent Dates] is a query name.

Am I missing something? Thanks
MIKE

Michel Walsh said:
Hi,


If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = CurrentDb.OpenRecordset("SELECT f1 FROM t1 WHERE
NumericalField=" & _
Me.ControlWIthNumericalValue ).Fields(0).Value
End If



Note: that would create an error if the query does not return any record.
You may use a DLookup( ) if all you need is to read a single table:

If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = DLookup("f1", "t1", "NumericalField=" &
Me.ControlWIthNumericalValue )
End If



should do the same as the previous solution, but wihtout error if there
is
no match (a NULL will be returned). The second argument of DLookup limits
you to a single table, or to a saved query.



Hoping it may help,
Vanderghast, Access MVP

Hello,

I am in a form and after selecting a value for one field, under the On
Click
property, want to run a query to choose a value to set the Default
Value
of
another field. I am thinking this is SQL language but am not sure if
it
goes
in Code or Macro or Expression. When I use Code and put SQL language
in
the
Visual Basic there, I get errors.

Any idea? Thanks for help!

MIKE
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Your query has parameter in the format: FORMS!FormName!ControlName

Using DoCmd, or the graphical edition, solves that parameter for you,
but using CurrentDb does not. (I should have seen it.) The first solution
would be to solve the parameter with something like:


Dim qdf As DAO.QueryDef
Dim param AS DAO.Parameter

Set qdf=CurrentDb.QueryDefs(" theNameOfYourQuery ")
For each qdf As qdf.Parameters
qdf.Value= eval(qdf.Name) ' <----- or other solution
Next qdf


ObjectName.Property=qdf.OpenRecordset( ).Fields(0).Value




The great thing is that DLookup solves the parameters
FORMS!formName!ControlName, so all that previous code can be changed by:


Object.Name.Property = DLookup("FieldName", "SavedQueryName")



Note that DLookup cannot accept an immediate SQL statement, just a table
name or a saved query.


Hoping it may help,
Vanderghast, Access MVP




Mike Binger said:
I am using that and the syntax still isn't right. The query is also set up
as an actual query, but when I try the OpenRecordset with that query name
in
quotes I get the run-time error '3061': Too few parameters. Expected 1.

MIKE

Michel Walsh said:
Hi,


The whole. long, syntax, would be:




Dim rst As DAO.Recordset
Set rst=CurrentDb.OpenRecordset( SQLstring )
ObjectName.Property = rst.Fields(0).Value
Set rst=Nothing


we make it shorter (one line of code) with:


ObjectName.Property = CurrentDb.OpenRecordset(
SQLstring).Fields(0).Value


Your code miss the ending: .Fields(0).Value


Note: you can make further enhancement as specifying you open the
recordset
for read only, and forward only (no backward mechanic-pointers to be
initialized), so the execution can be even faster, but that is
optimization.

Hoping it may help,
Vanderghast, Access MVP



Mike Binger said:
The code I am trying is:

Dim MyDB As Database
Set MyDB = CurrentDb()
[Begin Number].DefaultValue = MyDB.OpenRecordset
("SELECT Max(IIf([Check Log].[Date]=[Check Log Usage Most Recent
Dates].[Recent Date],
[End Number],0))+1 FROM [Check Log] LEFT JOIN [Check Log Usage Most
Recent
Dates] ON
[Check Log].[Check Use] = [Check Log Usage Most Recent Dates].Usage
GROUP BY [Check Log].[Check Use]
HAVING ((([Check Log].[Check Use])=[Forms]![Check Log]![Check Use]))")

This is giving me errors. [Check Log] is a table name, [Check Log
Usage
Most Recent Dates] is a query name.

Am I missing something? Thanks
MIKE

:

Hi,


If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = CurrentDb.OpenRecordset("SELECT f1 FROM t1
WHERE
NumericalField=" & _
Me.ControlWIthNumericalValue ).Fields(0).Value
End If



Note: that would create an error if the query does not return any
record.
You may use a DLookup( ) if all you need is to read a single table:

If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = DLookup("f1", "t1", "NumericalField=" &
Me.ControlWIthNumericalValue )
End If



should do the same as the previous solution, but wihtout error if
there
is
no match (a NULL will be returned). The second argument of DLookup
limits
you to a single table, or to a saved query.



Hoping it may help,
Vanderghast, Access MVP

Hello,

I am in a form and after selecting a value for one field, under the
On
Click
property, want to run a query to choose a value to set the Default
Value
of
another field. I am thinking this is SQL language but am not sure
if
it
goes
in Code or Macro or Expression. When I use Code and put SQL
language
in
the
Visual Basic there, I get errors.

Any idea? Thanks for help!

MIKE
Click to expand...
Click to expand...
Click to expand...
 
DLookup works! After the query was created, I just used the query field name
and the query name in DLookup. Then it wasn't the default value I needed
(that tried to create a new record), just the form field value itself.
Thanks so much!

MIKE

Michel Walsh said:
Hi,

Your query has parameter in the format: FORMS!FormName!ControlName

Using DoCmd, or the graphical edition, solves that parameter for you,
but using CurrentDb does not. (I should have seen it.) The first solution
would be to solve the parameter with something like:


Dim qdf As DAO.QueryDef
Dim param AS DAO.Parameter

Set qdf=CurrentDb.QueryDefs(" theNameOfYourQuery ")
For each qdf As qdf.Parameters
qdf.Value= eval(qdf.Name) ' <----- or other solution
Next qdf


ObjectName.Property=qdf.OpenRecordset( ).Fields(0).Value




The great thing is that DLookup solves the parameters
FORMS!formName!ControlName, so all that previous code can be changed by:


Object.Name.Property = DLookup("FieldName", "SavedQueryName")



Note that DLookup cannot accept an immediate SQL statement, just a table
name or a saved query.


Hoping it may help,
Vanderghast, Access MVP




Mike Binger said:
I am using that and the syntax still isn't right. The query is also set up
as an actual query, but when I try the OpenRecordset with that query name
in
quotes I get the run-time error '3061': Too few parameters. Expected 1.

MIKE

Michel Walsh said:
Hi,


The whole. long, syntax, would be:




Dim rst As DAO.Recordset
Set rst=CurrentDb.OpenRecordset( SQLstring )
ObjectName.Property = rst.Fields(0).Value
Set rst=Nothing


we make it shorter (one line of code) with:


ObjectName.Property = CurrentDb.OpenRecordset(
SQLstring).Fields(0).Value


Your code miss the ending: .Fields(0).Value


Note: you can make further enhancement as specifying you open the
recordset
for read only, and forward only (no backward mechanic-pointers to be
initialized), so the execution can be even faster, but that is
optimization.

Hoping it may help,
Vanderghast, Access MVP



The code I am trying is:

Dim MyDB As Database
Set MyDB = CurrentDb()
[Begin Number].DefaultValue = MyDB.OpenRecordset
("SELECT Max(IIf([Check Log].[Date]=[Check Log Usage Most Recent
Dates].[Recent Date],
[End Number],0))+1 FROM [Check Log] LEFT JOIN [Check Log Usage Most
Recent
Dates] ON
[Check Log].[Check Use] = [Check Log Usage Most Recent Dates].Usage
GROUP BY [Check Log].[Check Use]
HAVING ((([Check Log].[Check Use])=[Forms]![Check Log]![Check Use]))")

This is giving me errors. [Check Log] is a table name, [Check Log
Usage
Most Recent Dates] is a query name.

Am I missing something? Thanks
MIKE

:

Hi,


If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = CurrentDb.OpenRecordset("SELECT f1 FROM t1
WHERE
NumericalField=" & _
Me.ControlWIthNumericalValue ).Fields(0).Value
End If



Note: that would create an error if the query does not return any
record.
You may use a DLookup( ) if all you need is to read a single table:

If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = DLookup("f1", "t1", "NumericalField=" &
Me.ControlWIthNumericalValue )
End If



should do the same as the previous solution, but wihtout error if
there
is
no match (a NULL will be returned). The second argument of DLookup
limits
you to a single table, or to a saved query.



Hoping it may help,
Vanderghast, Access MVP

Hello,

I am in a form and after selecting a value for one field, under the
On
Click
property, want to run a query to choose a value to set the Default
Value
of
another field. I am thinking this is SQL language but am not sure
if
it
goes
in Code or Macro or Expression. When I use Code and put SQL
language
in
the
Visual Basic there, I get errors.

Any idea? Thanks for help!

MIKE
Click to expand...
Click to expand...
Click to expand...
 
Hi,


Just in case you tried my online code, you would be happier with this
version:


Set qdf=CurrentDb.QueryDefs(" theNameOfYourQuery ")
For each PARAM As qdf.Parameters
PARAM.Value= eval(PARAM.Name) ' <----- or other solution
Next qdf



I wrongly used variable qdf while it has to be varriable Param.


Vanderghast, Access MVP


Mike Binger said:
DLookup works! After the query was created, I just used the query field
name
and the query name in DLookup. Then it wasn't the default value I needed
(that tried to create a new record), just the form field value itself.
Thanks so much!

MIKE

Michel Walsh said:
Hi,

Your query has parameter in the format: FORMS!FormName!ControlName

Using DoCmd, or the graphical edition, solves that parameter for you,
but using CurrentDb does not. (I should have seen it.) The first
solution
would be to solve the parameter with something like:


Dim qdf As DAO.QueryDef
Dim param AS DAO.Parameter

Set qdf=CurrentDb.QueryDefs(" theNameOfYourQuery ")
For each qdf As qdf.Parameters
qdf.Value= eval(qdf.Name) ' <----- or other solution
Next qdf


ObjectName.Property=qdf.OpenRecordset( ).Fields(0).Value




The great thing is that DLookup solves the parameters
FORMS!formName!ControlName, so all that previous code can be changed by:


Object.Name.Property = DLookup("FieldName", "SavedQueryName")



Note that DLookup cannot accept an immediate SQL statement, just a table
name or a saved query.


Hoping it may help,
Vanderghast, Access MVP




Mike Binger said:
I am using that and the syntax still isn't right. The query is also set
up
as an actual query, but when I try the OpenRecordset with that query
name
in
quotes I get the run-time error '3061': Too few parameters. Expected
1.

MIKE

:

Hi,


The whole. long, syntax, would be:




Dim rst As DAO.Recordset
Set rst=CurrentDb.OpenRecordset( SQLstring )
ObjectName.Property = rst.Fields(0).Value
Set rst=Nothing


we make it shorter (one line of code) with:


ObjectName.Property = CurrentDb.OpenRecordset(
SQLstring).Fields(0).Value


Your code miss the ending: .Fields(0).Value


Note: you can make further enhancement as specifying you open the
recordset
for read only, and forward only (no backward mechanic-pointers to be
initialized), so the execution can be even faster, but that is
optimization.

Hoping it may help,
Vanderghast, Access MVP



The code I am trying is:

Dim MyDB As Database
Set MyDB = CurrentDb()
[Begin Number].DefaultValue = MyDB.OpenRecordset
("SELECT Max(IIf([Check Log].[Date]=[Check Log Usage Most Recent
Dates].[Recent Date],
[End Number],0))+1 FROM [Check Log] LEFT JOIN [Check Log Usage Most
Recent
Dates] ON
[Check Log].[Check Use] = [Check Log Usage Most Recent Dates].Usage
GROUP BY [Check Log].[Check Use]
HAVING ((([Check Log].[Check Use])=[Forms]![Check Log]![Check
Use]))")

This is giving me errors. [Check Log] is a table name, [Check Log
Usage
Most Recent Dates] is a query name.

Am I missing something? Thanks
MIKE

:

Hi,


If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = CurrentDb.OpenRecordset("SELECT f1 FROM t1
WHERE
NumericalField=" & _
Me.ControlWIthNumericalValue ).Fields(0).Value
End If



Note: that would create an error if the query does not return any
record.
You may use a DLookup( ) if all you need is to read a single table:

If Not IsNull( Me.ControlWIthNumericalValue ) Then
Me.OtherField = DLookup("f1", "t1", "NumericalField=" &
Me.ControlWIthNumericalValue )
End If



should do the same as the previous solution, but wihtout error if
there
is
no match (a NULL will be returned). The second argument of DLookup
limits
you to a single table, or to a saved query.



Hoping it may help,
Vanderghast, Access MVP

message
Hello,

I am in a form and after selecting a value for one field, under
the
On
Click
property, want to run a query to choose a value to set the
Default
Value
of
another field. I am thinking this is SQL language but am not
sure
if
it
goes
in Code or Macro or Expression. When I use Code and put SQL
language
in
the
Visual Basic there, I get errors.

Any idea? Thanks for help!

MIKE
Click to expand...
Click to expand...
Click to expand...
 
Back
Top