Method Use VB code instead of Insert query??

R

RoadKyng

Greetings,

I have a few forms that use unbound combo boxes to set matching criteria for
an insert query. Three tables are in work here. The combo boxes are used to
select data from [AD - Main tbl], and a query matches certain fields in the
[JSLLC Aircraft tbl] then creates a record in the [AD - Aircraft sub tbl],
using data from the first two tables. I would like to learn to perform this
type function in VB and get away from complicated queries. However I have not
located a proper command to use and am having trouble understanding some of
the code I find on this site.


My query is as follows. Is there a fairly simple method to code this in VB?

INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber, ADcatagory,
ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout )
SELECT [JSLLC Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD -
Main tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint,
[AD - Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [JSLLC
Aircraft tbl].Registration, [JSLLC Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND
FROM [AD - Main tbl], [JSLLC Aircraft tbl]
WHERE ((([JSLLC Aircraft tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like "ENGINE") AND (([JSLLC Aircraft
tbl].CertStatus)<>"off certificate"));

Secondly - I named these tables a few years ago before I learned proper
naming conventions. Is there a way to globally change a table name that will
affect all code, queries and forms?


Thank You
 
K

Klatuu

First, here is a link to a site where you can downloand a really good find
and replace utility that will change a name in all your objects. There is a
free demo version, but the full version is only $39.00

http://www.rickworld.com/download.html

As to putting the SQL in VBA, Open the query in SQL view, copy the code, and
paste it into your VBA module. You will, of course, have to put the proper
quotes to make it into a string variable. Then use the Execute method to run
the query.

Currentdb.Execute(strSQL), dbFailOnError
 
R

RoadKyng

Thank you
I constructed the string as below. I am getting an error that states Runtime
error 3061. Too few parameters. Expected 2"

Is my syntax off?



strSQL = "INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber,
ADcatagory, ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout ) " & _
"SELECT [Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD - Main
tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint, [AD -
Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [Aircraft
tbl].Registration, [Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND " & _
"FROM [AD - Main tbl], [Aircraft tbl] WHERE ((([Aircraft
tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like 'ENGINE') AND (([Aircraft
tbl].CertStatus)<>'off certificate'));"


CurrentDb.Execute strSQL, dbFailOnError
--
GmH


Klatuu said:
First, here is a link to a site where you can downloand a really good find
and replace utility that will change a name in all your objects. There is a
free demo version, but the full version is only $39.00

http://www.rickworld.com/download.html

As to putting the SQL in VBA, Open the query in SQL view, copy the code, and
paste it into your VBA module. You will, of course, have to put the proper
quotes to make it into a string variable. Then use the Execute method to run
the query.

Currentdb.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


RoadKyng said:
Greetings,

I have a few forms that use unbound combo boxes to set matching criteria for
an insert query. Three tables are in work here. The combo boxes are used to
select data from [AD - Main tbl], and a query matches certain fields in the
[JSLLC Aircraft tbl] then creates a record in the [AD - Aircraft sub tbl],
using data from the first two tables. I would like to learn to perform this
type function in VB and get away from complicated queries. However I have not
located a proper command to use and am having trouble understanding some of
the code I find on this site.


My query is as follows. Is there a fairly simple method to code this in VB?

INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber, ADcatagory,
ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout )
SELECT [JSLLC Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD -
Main tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint,
[AD - Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [JSLLC
Aircraft tbl].Registration, [JSLLC Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND
FROM [AD - Main tbl], [JSLLC Aircraft tbl]
WHERE ((([JSLLC Aircraft tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like "ENGINE") AND (([JSLLC Aircraft
tbl].CertStatus)<>"off certificate"));

Secondly - I named these tables a few years ago before I learned proper
naming conventions. Is there a way to globally change a table name that will
affect all code, queries and forms?


Thank You
Click to expand...
Click to expand...
 
K

Klatuu

I don't see anything obvious. You may want to check all your spelling and
brackets. I notice you use a lot of spaces in table and field names (bad
idea), so you could possibly be inducing an error there.

I would try making sure the query works in the query builder first, then
when it does, move it back to VBA. Just open the form so it has the
references while you are testing.

Now, a question for your. Why do you find it necessary to use VBA rather
than just executing a stored query? Stored queries actually execute faster.
The only time I build a query in VBA is when a lot of logic is involved at
runtime to create a query.

--
Dave Hargis, Microsoft Access MVP


RoadKyng said:
Thank you
I constructed the string as below. I am getting an error that states Runtime
error 3061. Too few parameters. Expected 2"

Is my syntax off?



strSQL = "INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber,
ADcatagory, ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout ) " & _
"SELECT [Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD - Main
tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint, [AD -
Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [Aircraft
tbl].Registration, [Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND " & _
"FROM [AD - Main tbl], [Aircraft tbl] WHERE ((([Aircraft
tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like 'ENGINE') AND (([Aircraft
tbl].CertStatus)<>'off certificate'));"


CurrentDb.Execute strSQL, dbFailOnError
--
GmH


Klatuu said:
First, here is a link to a site where you can downloand a really good find
and replace utility that will change a name in all your objects. There is a
free demo version, but the full version is only $39.00

http://www.rickworld.com/download.html

As to putting the SQL in VBA, Open the query in SQL view, copy the code, and
paste it into your VBA module. You will, of course, have to put the proper
quotes to make it into a string variable. Then use the Execute method to run
the query.

Currentdb.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


RoadKyng said:
Greetings,

I have a few forms that use unbound combo boxes to set matching criteria for
an insert query. Three tables are in work here. The combo boxes are used to
select data from [AD - Main tbl], and a query matches certain fields in the
[JSLLC Aircraft tbl] then creates a record in the [AD - Aircraft sub tbl],
using data from the first two tables. I would like to learn to perform this
type function in VB and get away from complicated queries. However I have not
located a proper command to use and am having trouble understanding some of
the code I find on this site.


My query is as follows. Is there a fairly simple method to code this in VB?

INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber, ADcatagory,
ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout )
SELECT [JSLLC Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD -
Main tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint,
[AD - Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [JSLLC
Aircraft tbl].Registration, [JSLLC Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND
FROM [AD - Main tbl], [JSLLC Aircraft tbl]
WHERE ((([JSLLC Aircraft tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like "ENGINE") AND (([JSLLC Aircraft
tbl].CertStatus)<>"off certificate"));

Secondly - I named these tables a few years ago before I learned proper
naming conventions. Is there a way to globally change a table name that will
affect all code, queries and forms?


Thank You
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

The problem is in your where clause. The Jet engine won't recognize the
references to the form controls. You can fix that by doing the following
for the where clause

StrSQL = StrSQL & " WHERE [Aircraft tbl].AircraftType='" & _
[Forms]![REPORTING - AirworthinessDirectives]![AIRCRAFTmodel] & "' " & _
" AND [AD - Main tbl].ADNumber='" & _
[Forms]![REPORTING - AirworthinessDirectives]![ADnumber] & "' " & _
" AND [AD - Main tbl].CATAGORY Not Like 'ENGINE' " & _
" AND [Aircraft tbl].CertStatus <>'off certificate' ;"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
R

RoadKyng

#1 Ignorance. I thought a VB query would run faster. But I can also pass
different variables to the query in VB that look pretty ugly in a standard
Query.
I am one of those learn on the fly types that got handed a database to
manage. A trained programmer I am not.

--
GmH


Klatuu said:
Now, a question for your. Why do you find it necessary to use VBA rather
than just executing a stored query? Stored queries actually execute faster.
The only time I build a query in VBA is when a lot of logic is involved at
runtime to create a query.

--
Dave Hargis, Microsoft Access MVP


RoadKyng said:
Thank you
I constructed the string as below. I am getting an error that states Runtime
error 3061. Too few parameters. Expected 2"

Is my syntax off?



strSQL = "INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber,
ADcatagory, ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout ) " & _
"SELECT [Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD - Main
tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint, [AD -
Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [Aircraft
tbl].Registration, [Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND " & _
"FROM [AD - Main tbl], [Aircraft tbl] WHERE ((([Aircraft
tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like 'ENGINE') AND (([Aircraft
tbl].CertStatus)<>'off certificate'));"


CurrentDb.Execute strSQL, dbFailOnError
--
GmH


Klatuu said:
First, here is a link to a site where you can downloand a really good find
and replace utility that will change a name in all your objects. There is a
free demo version, but the full version is only $39.00

http://www.rickworld.com/download.html

As to putting the SQL in VBA, Open the query in SQL view, copy the code, and
paste it into your VBA module. You will, of course, have to put the proper
quotes to make it into a string variable. Then use the Execute method to run
the query.

Currentdb.Execute(strSQL), dbFailOnError
--
Dave Hargis, Microsoft Access MVP


:

Greetings,

I have a few forms that use unbound combo boxes to set matching criteria for
an insert query. Three tables are in work here. The combo boxes are used to
select data from [AD - Main tbl], and a query matches certain fields in the
[JSLLC Aircraft tbl] then creates a record in the [AD - Aircraft sub tbl],
using data from the first two tables. I would like to learn to perform this
type function in VB and get away from complicated queries. However I have not
located a proper command to use and am having trouble understanding some of
the code I find on this site.


My query is as follows. Is there a fairly simple method to code this in VB?

INSERT INTO [AD - Aircraft sub tbl] ( aircraftMODEL, ADnumber, ADcatagory,
ADtitle, ADinitDUEpnt, ADeffDATE, ADrecurINTRVL, REGISTRATION,
aircraftSERIALnumber, MethodofCompliance, componentMANUFACTURER,
componentDESCRIPTION, Supersedes, AcftSNin, AcftSNout )
SELECT [JSLLC Aircraft tbl].AircraftType, [AD - Main tbl].ADNumber, [AD -
Main tbl].CATAGORY, [AD - Main tbl].Title, [AD - Main tbl].intialDUEpoint,
[AD - Main tbl].EffectiveDate, [AD - Main tbl].recurringINTERVAL, [JSLLC
Aircraft tbl].Registration, [JSLLC Aircraft tbl].SerialNumber, [AD - Main
tbl].complianceMETHOD, [AD - Main tbl].manufacturer, [AD - Main
tbl].componentAPPLICABILITY, [AD - Main tbl].Supersedes, [AD - Main
tbl].acftSNrangeSTART, [AD - Main tbl].acftSNrangeEND
FROM [AD - Main tbl], [JSLLC Aircraft tbl]
WHERE ((([JSLLC Aircraft tbl].AircraftType)=[Forms]![REPORTING -
AirworthinessDirectives]![AIRCRAFTmodel]) AND (([AD - Main
tbl].ADNumber)=[Forms]![REPORTING - AirworthinessDirectives]![ADnumber]) AND
(([AD - Main tbl].CATAGORY) Not Like "ENGINE") AND (([JSLLC Aircraft
tbl].CertStatus)<>"off certificate"));

Secondly - I named these tables a few years ago before I learned proper
naming conventions. Is there a way to globally change a table name that will
affect all code, queries and forms?


Thank You
Click to expand...
Click to expand...
Click to expand...
 
Top