Converting query to VBA

  • Thread starter Thread starter Tony Williams
  • Start date Start date
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
 
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...
 
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...
 
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...
 
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...
 
Back
Top