Converting query to VBA

T

Tony Williams

I know there is a facility in Access 2000 to convert macros to VBA but is
there a facility to convert queries to VBA? I have an update query that is
run from a command button on a form but I want the procedure that the query
runs to run as the AfterUpdate property of another form. Is that possible?
Here is my SQL.

UPDATE tblmaintabs SET tblmaintabs.txtDomfacsole =
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2],[txtDomfacsoleqtr],0)-DLook
Up("[txtDomfacsoleqtr]","[tblmaintabs]","[txtMonthLabel]=[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'"), tblmaintabs.txtDomfacpart
=
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2],[txtDomfacpartqtr],0)-DLook
Up("[txtDomfacpartqtr]","[tblmaintabs]","[txtMonthLabel]=[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'")
WHERE (((tblmaintabs.txtMonthlabel)=[Forms]![frmdate4]![txtqtr2]));

I'm a newbie at VBA so please handle me with care!

Thanks
Tony
 
G

Guest

hi,
VB works real well with SQL. SQL can be use in many places
working with VB. Look up the DoCmd.RunSQL command in help.
I think that may be all you need.
-----Original Message-----
I know there is a facility in Access 2000 to convert macros to VBA but is
there a facility to convert queries to VBA? I have an update query that is
run from a command button on a form but I want the procedure that the query
runs to run as the AfterUpdate property of another form. Is that possible?
Here is my SQL.

UPDATE tblmaintabs SET tblmaintabs.txtDomfacsole =
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2], [txtDomfacsoleqtr],0)-DLook
Up("[txtDomfacsoleqtr]","[tblmaintabs]","[txtMonthLabel]=
Click to expand...
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'"), tblmaintabs.txtDomfacpart
=
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2], [txtDomfacpartqtr],0)-DLook
Up("[txtDomfacpartqtr]","[tblmaintabs]","[txtMonthLabel]=
Click to expand...
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'")
WHERE (((tblmaintabs.txtMonthlabel)=[Forms]![frmdate4]! [txtqtr2]));

I'm a newbie at VBA so please handle me with care!

Thanks
Tony


.
Click to expand...
 
T

Tony Williams

Thanks for that it looks pretty straight forward. However I have one problem
in that as you see my current SQL refers to two dates which are input on a
form to run the query. The first date is the current value of a field
txtmonthlabel and the second date is txtmonthlabel minus three months. I'm
struggling as to how I'd change the SQL statement to remove the need for the
first input form. When my second form is open the field txtmonthlabel is
available for the code.
Can you help?
Thanks
Tony
hi,
VB works real well with SQL. SQL can be use in many places
working with VB. Look up the DoCmd.RunSQL command in help.
I think that may be all you need.
-----Original Message-----
I know there is a facility in Access 2000 to convert macros to VBA but is
there a facility to convert queries to VBA? I have an update query that is
run from a command button on a form but I want the procedure that the query
runs to run as the AfterUpdate property of another form. Is that possible?
Here is my SQL.

UPDATE tblmaintabs SET tblmaintabs.txtDomfacsole =
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2], [txtDomfacsoleqtr],0)-DLook
Up("[txtDomfacsoleqtr]","[tblmaintabs]","[txtMonthLabel]=
Click to expand...
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'"), tblmaintabs.txtDomfacpart
=
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2], [txtDomfacpartqtr],0)-DLook
Up("[txtDomfacpartqtr]","[tblmaintabs]","[txtMonthLabel]=
Click to expand...
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'")
WHERE (((tblmaintabs.txtMonthlabel)=[Forms]![frmdate4]! [txtqtr2]));

I'm a newbie at VBA so please handle me with care!

Thanks
Tony


.
Click to expand...
Click to expand...
 
G

Guest

hi again,
yes. if the field txtmonthlable is available on form2 then
all you would have to do is change the form reference in
the sql statement.
-----Original Message-----
Thanks for that it looks pretty straight forward. However I have one problem
in that as you see my current SQL refers to two dates which are input on a
form to run the query. The first date is the current value of a field
txtmonthlabel and the second date is txtmonthlabel minus three months. I'm
struggling as to how I'd change the SQL statement to remove the need for the
first input form. When my second form is open the field txtmonthlabel is
available for the code.
Can you help?
Thanks
Tony
hi,
VB works real well with SQL. SQL can be use in many places
working with VB. Look up the DoCmd.RunSQL command in help.
I think that may be all you need.
-----Original Message-----
I know there is a facility in Access 2000 to convert macros to VBA but is
there a facility to convert queries to VBA? I have an update query that is
run from a command button on a form but I want the procedure that the query
runs to run as the AfterUpdate property of another
Click to expand...
form.
Is that possible?
Here is my SQL.

UPDATE tblmaintabs SET tblmaintabs.txtDomfacsole =
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2], [txtDomfacsoleqtr],0)-DLook
Up
Click to expand...
("[txtDomfacsoleqtr]","[tblmaintabs]","[txtMonthLabel]=
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'"), tblmaintabs.txtDomfacpart
=
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2],
Click to expand...
[txtDomfacpartqtr],0)-DLook
Up
Click to expand...
("[txtDomfacpartqtr]","[tblmaintabs]","[txtMonthLabel]=
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'")
WHERE (((tblmaintabs.txtMonthlabel)=[Forms]![frmdate4]! [txtqtr2]));

I'm a newbie at VBA so please handle me with care!

Thanks
Tony


.
Click to expand...
Click to expand...


.
Click to expand...
 
T

Tony Williams

Thanks in the case of the second date which is three months before
txtmonthlabel could I substitute a DateAdd expression?
Thanks
Tony
hi again,
yes. if the field txtmonthlable is available on form2 then
all you would have to do is change the form reference in
the sql statement.
-----Original Message-----
Thanks for that it looks pretty straight forward. However I have one problem
in that as you see my current SQL refers to two dates which are input on a
form to run the query. The first date is the current value of a field
txtmonthlabel and the second date is txtmonthlabel minus three months. I'm
struggling as to how I'd change the SQL statement to remove the need for the
first input form. When my second form is open the field txtmonthlabel is
available for the code.
Can you help?
Thanks
Tony
hi,
VB works real well with SQL. SQL can be use in many places
working with VB. Look up the DoCmd.RunSQL command in help.
I think that may be all you need.

-----Original Message-----
I know there is a facility in Access 2000 to convert
macros to VBA but is
there a facility to convert queries to VBA? I have an
update query that is
run from a command button on a form but I want the
procedure that the query
runs to run as the AfterUpdate property of another form.
Is that possible?
Here is my SQL.

UPDATE tblmaintabs SET tblmaintabs.txtDomfacsole =
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2],
[txtDomfacsoleqtr],0)-DLook
Up ("[txtDomfacsoleqtr]","[tblmaintabs]","[txtMonthLabel]=
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'"),
tblmaintabs.txtDomfacpart
=
IIf([txtMonthLabel]=[Forms]![frmdate4]![txtqtr2],
[txtDomfacpartqtr],0)-DLook
Up ("[txtDomfacpartqtr]","[tblmaintabs]","[txtMonthLabel]=
[Forms]![frmdate4]!
[txtqtr3]and txtCompany='" & [txtCompany] & "'")
WHERE (((tblmaintabs.txtMonthlabel)=[Forms]![frmdate4]!
[txtqtr2]));

I'm a newbie at VBA so please handle me with care!

Thanks
Tony


.
Click to expand...


.
Click to expand...
Click to expand...
 

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

Calculating quarterly values from YTD values 5
Code will work on one table and form but not on another? 19
Problem with subform showing too many records 4
HELP! I'm really struggling to understand DLookup! 0
Help with Message box code 2
IIF Expression returning Zero Records in Query 2
Read Only Query, need a little help 1
query to show where no data in a stated column 0

Top