APPEND QUERY FED FROM FORM DOES NOT WORK

[BlueWolverine]

Hello,
MS ACCESS 2003 XPPro

So I have this form with some fields that the user fills in by hand. They
don't link back to a table or anything but some VBA calls an append query
that takes the two fields that serve as the key for the table i care about,
and appends them to the table I care about.

Only it's not working.

Here is the SQL for the append query.

INSERT INTO t_FuelCardInventory ( FuelCardProvider, FuelCardNo )
SELECT Forms!f_AddFuelCard!FCP AS vFCP, Forms!f_AddFuelCard!FCN AS vFCN
FROM t_FuelCardInventory
GROUP BY Forms!f_AddFuelCard!FCP, Forms!f_AddFuelCard!FCN;

Here is the entirety of the VBA code that executes it. The line calling the
above query is surrounded by <<<>>>

THANK YOU!!!!


Option Compare Database

Private Sub cmd_AddFuelCard_Click()
Dim mydb As Database, rs As Recordset, tqn As String
tqn = "q_AddFuelCard_IsFC_inInv"
Dim rs1 As Recordset, tqn1 As String
tqn1 = "q_AddFuelCard_DupFuelCard"
Dim rs2 As Recordset, tqn2 As String
tqn2 = "q_AddFuelCards_VINs_OutSVC"

Set mydb = CurrentDb
Set rs = mydb.OpenRecordset(tqn)
Set rs1 = mydb.OpenRecordset(tqn1)
Set rs2 = mydb.OpenRecordset(tqn2)

DoCmd.SetWarnings False
'if fuel card is not in the inventory
If rs.EOF Or rs.BOF Then

If IsNull(Me.FCVIN.Value) Then
'ADD FUEL CARD TO INVENTORY, UNASSIGNED
DoCmd.OpenQuery "q_AddFuelCard_Append_NewFuelCard"
DoCmd.OpenQuery "q_AddFuelCard_Update_NewFuelCard"

ElseIf Not IsNull(Me.FCVIN.Value) Then

'If fuel card does not have a fuel card of the same type already
then
'And VIN is not OutSvcDate out of service!!!

If (rs2.EOF Or rs2.BOF) Then
If (rs1.EOF Or rs1.BOF) Then

'Variables starting with v_ do not matter and are not used for anything.
They absorb the values from the form. And this part works... v_VIN pulls
the value out of the form that it should, etc. The appendquery goes directly
to the form values though, not the variables used here.
v_VIN = FCVIN
v_FCN = FCN
v_FCP = FCP
v_FCS = FCS
v_FCAN = FCAN
'ADD FUEL CARD TO INVENTORY
<<<<<<<<<DoCmd.OpenQuery "q_AddFuelCard_Append_NewFuelCard">>>>>>
'UPDATE FUEL CARD to be assigned to current vin
DoCmd.OpenQuery "q_AddFuelCard_Update_NewFuelCard_toVIN"
'APPEND Fuel Card to FCHistory
DoCmd.OpenQuery "q_AddFuelCard_append_NewFuelCard_toVIN"

Else
bob = MsgBox("VIN already has a " & Me.FCP.Value & "
fuel card attached to it. Exiting...", vbCritical, "There is already a " &
Me.FCP.Value & " fuel card on that vehicle")
Exit Sub
End If
Else
MsgBox "VIN has been put out of service. You cannot add a
fuel card to it. This fuel card will NOT be added to the inventory.",
vbCritical, "VIN out of Service"
Exit Sub
End If
End If

'else if the fuel card IS in the inventory already
Else
Dim rs3 As Recordset, tqn3 As String
tqn3 = "q_AddFuelCards_AssignedFuelCards"
Set rs3 = mydb.OpenRecordset(tqn3)

'if the fuel card is not already assigned then
If (rs3.EOF Or rs3.BOF) Then

'if vehicle is out of service, having been in service
If (rs2.EOF Or rs2.BOF) Then

'If vehicle does not have a fuel card of the same type
already then
If (rs1.EOF Or rs1.BOF) Then
'UPDATE FUEL CARD to be assigned to current vin
DoCmd.OpenQuery "q_AddFuelCard_OldFuelCard_toNewVIN"
'APPEND Fuel Card to FCHistory
DoCmd.OpenQuery "q_AddFuelCard_append_NewFuelCard_toVIN"
Else
'error message vehicle already has a fuel card of that
type
bob = MsgBox("VIN already has a " & Me.FCP.Value & "
fuel card attached to it. Exiting...", vbCritical, "There is already a " &
Me.FCP.Value & " fuel card on that vehicle")
Exit Sub
End If

Else
MsgBox "VIN has been put out of service. You cannot add a
fuel card to it. This fuel card will NOT be added to the inventory.",
vbCritical, "VIN out of Service"
Exit Sub
End If

Else
'if the fuel card is already assigned then

'error message "fuel card already assigned to another unit."
bob = MsgBox("Error" & Me.FCP.Value & " " & Me.FCN.Value & " is
already assigned. No data will be changed.", vbCritical, "Fuel Card already
assigned")
Exit Sub
End If


End If
Dim fname As String
fname = Me.PrevFormName.Value
DoCmd.Close acForm, fname
DoCmd.OpenForm fname
Forms("" & fname & "").Refresh

End Sub

 

[Conan Kelly]

BlueWolverine,

INSERT INTO t_FuelCardInventory ( FuelCardProvider, FuelCardNo )
...
FROM t_FuelCardInventory
GROUP BY Forms!f_AddFuelCard!FCP, Forms!f_AddFuelCard!FCN;

Correct me if I'm wrong, but it looks like your inserting into the same
object you are selecting from.

I don't have any experience with what you are trying to do. I don't know if
it is even possible. Maybe changing...

FROM t_FuelCardInventory

....to...

FROM Forms!f_AddFuelCard

....MIGHT work. I don't know. Like I said...no experience. Don't know if
it is possible to create a SQL statment based off of a form.

Also, how is your form set up. Is it just a simple form with a couple/few
text boxes. Is it only meant to show/enter one record worth of data? If
so, it seems the GROUP BY clause in your SQL statement is unnecessary.

You might try a statement like the this:

INSERT t_FuelCardInventory
VALUES (FuelCardProvider, FuelCardNo)

I think this is the correct syntax for inserting values, that are not
located in a specific file or object, into a table for SQL Server. Don't
know if it will work for Access. And the statement I provide will probably
enter those values into the first 2 fields/columns of the table. If those
values are not lining up with the right columns, you might have to add other
commas, NULL's, empty strings, 0's, etc... in the right places, depending on
the data types/properties of the columns. For example, lets say the data
from those 2 text boxes on the form need to go into columns 4 & 5 out of
eight columns. You might have to try one of the following VALUES clauses
(or a combination of them) to get it to work:

VALUES ( , , ,Forms!f_AddFuelCard!FCP , Forms!f_AddFuelCard!FCN , , , )
VALUES (NULL ,NULL ,NULL ,FuelCardProvider, FuelCardNo,NULL ,NULL ,NULL )
VALUES ( "","" ,0 ,FuelCardProvider, FuelCardNo, 0, 0, "")

I woul first experiment with the INSERT ... VALUES query in the SQL view of
a query, just typing in values first (not refering to controls on a form),
to see if Access recognizes INSERT...VALUES and to make sure it inserts
correctly. Then get it to work with references to controls on a form. The
adjust the VBA code so it will construct the INSERT...VALUES statement
correcly.

HTH,

Conan

 

[BlueWolverine]

I was looking for the obvious screw up because I have done it before and lo
and behold you can't inserto into the same thing as from.

By changing the the FROM statement to an arbitrary table <> the one values
are going into, I got it to work perfectly!

THANK YOU SO MUCH
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!

BlueWolverine,

INSERT INTO t_FuelCardInventory ( FuelCardProvider, FuelCardNo )
...
FROM t_FuelCardInventory
GROUP BY Forms!f_AddFuelCard!FCP, Forms!f_AddFuelCard!FCN;

Correct me if I'm wrong, but it looks like your inserting into the same
object you are selecting from.

I don't have any experience with what you are trying to do. I don't know if
it is even possible. Maybe changing...

FROM t_FuelCardInventory

....to...

FROM Forms!f_AddFuelCard

....MIGHT work. I don't know. Like I said...no experience. Don't know if
it is possible to create a SQL statment based off of a form.

Also, how is your form set up. Is it just a simple form with a couple/few
text boxes. Is it only meant to show/enter one record worth of data? If
so, it seems the GROUP BY clause in your SQL statement is unnecessary.

You might try a statement like the this:

INSERT t_FuelCardInventory
VALUES (FuelCardProvider, FuelCardNo)

I think this is the correct syntax for inserting values, that are not
located in a specific file or object, into a table for SQL Server. Don't
know if it will work for Access. And the statement I provide will probably
enter those values into the first 2 fields/columns of the table. If those
values are not lining up with the right columns, you might have to add other
commas, NULL's, empty strings, 0's, etc... in the right places, depending on
the data types/properties of the columns. For example, lets say the data
from those 2 text boxes on the form need to go into columns 4 & 5 out of
eight columns. You might have to try one of the following VALUES clauses
(or a combination of them) to get it to work:

VALUES ( , , ,Forms!f_AddFuelCard!FCP , Forms!f_AddFuelCard!FCN , , , )
VALUES (NULL ,NULL ,NULL ,FuelCardProvider, FuelCardNo,NULL ,NULL ,NULL )
VALUES ( "","" ,0 ,FuelCardProvider, FuelCardNo, 0, 0, "")

I woul first experiment with the INSERT ... VALUES query in the SQL view of
a query, just typing in values first (not refering to controls on a form),
to see if Access recognizes INSERT...VALUES and to make sure it inserts
correctly. Then get it to work with references to controls on a form. The
adjust the VBA code so it will construct the INSERT...VALUES statement
correcly.

HTH,

Conan

 

[Conan Kelly]

WOOPS!!! There was an error in my first reply. The following is wrong:

INSERT t_FuelCardInventory
VALUES (FuelCardProvider, FuelCardNo)

It should have been something like:

INSERT t_FuelCardInventory
VALUES (Forms!f_AddFuelCard!FCP , Forms!f_AddFuelCard!FCN )

Also, MAYBE something like the following MIGHT work too (no experience with
these statements...just guessing):

INSERT INTO t_FuelCardInventory ( FuelCardProvider, FuelCardNo )
VALUES (Forms!f_AddFuelCard!FCP , Forms!f_AddFuelCard!FCN )

HTH,

Conan

BlueWolverine,

INSERT INTO t_FuelCardInventory ( FuelCardProvider, FuelCardNo )
...
FROM t_FuelCardInventory
GROUP BY Forms!f_AddFuelCard!FCP, Forms!f_AddFuelCard!FCN;

Correct me if I'm wrong, but it looks like your inserting into the same
object you are selecting from.

I don't have any experience with what you are trying to do. I don't know
if it is even possible. Maybe changing...

FROM t_FuelCardInventory

...to...

FROM Forms!f_AddFuelCard

...MIGHT work. I don't know. Like I said...no experience. Don't know if
it is possible to create a SQL statment based off of a form.

Also, how is your form set up. Is it just a simple form with a couple/few
text boxes. Is it only meant to show/enter one record worth of data? If
so, it seems the GROUP BY clause in your SQL statement is unnecessary.

You might try a statement like the this:

INSERT t_FuelCardInventory
VALUES (FuelCardProvider, FuelCardNo)

I think this is the correct syntax for inserting values, that are not
located in a specific file or object, into a table for SQL Server. Don't
know if it will work for Access. And the statement I provide will
probably enter those values into the first 2 fields/columns of the table.
If those values are not lining up with the right columns, you might have
to add other commas, NULL's, empty strings, 0's, etc... in the right
places, depending on the data types/properties of the columns. For
example, lets say the data from those 2 text boxes on the form need to go
into columns 4 & 5 out of eight columns. You might have to try one of the
following VALUES clauses (or a combination of them) to get it to work:

VALUES ( , , ,Forms!f_AddFuelCard!FCP , Forms!f_AddFuelCard!FCN , , , )
VALUES (NULL ,NULL ,NULL ,FuelCardProvider, FuelCardNo,NULL ,NULL ,NULL )
VALUES ( "","" ,0 ,FuelCardProvider, FuelCardNo, 0, 0, "")

I woul first experiment with the INSERT ... VALUES query in the SQL view
of a query, just typing in values first (not refering to controls on a
form), to see if Access recognizes INSERT...VALUES and to make sure it
inserts correctly. Then get it to work with references to controls on a
form. The adjust the VBA code so it will construct the INSERT...VALUES
statement correcly.

HTH,

Conan