Append/update Query with two sources

[cableguy47905 via AccessMonster.com]

There are many postings dealing with append queries and they have helped a
great deal with the other one I was working on, but this one is different.

I am wondering if it is possible to run an append query from code with two
different sources for the data. There will be three values written to the
table, but two are coming from a form and one is coming from a query.

Background:

The table is an associative entity with three values all set at the primary
key and duplicates allowed.
ContactID
CompanyID
LocationID

All three values are on the form, and would be used straight from the form
for an append query, but if a check box is checked for all locations, then I
want it to run a query on the TBL_CompanyContractLocation with the criteria
being the company ID. The only data out of that table that I want is the
locationID.

This means that if the chkAllLocs is checked then I would use the ContactID
and CompanyID from the form, and then the multiple LocationID's from the
query.

Is this possible with my train of thought right now?

 

[SteveD]

Here's one way --- have two append queries,
1) Data from the form.
2) Data from the form AND the data from the Location table.

At the point where the trigger (button) that runs the query, in the have an
IF statment in the Event Procedure that will select the correct query based
upon the value of the checkbox.

Something like:
'comment - append data based upon checkbox
If me.chkAllLocs = True then
docmd.runquery "qry_all locations"
Else
docmd.openquery "qry_formlocation"
End if

See if that works for you.

SteveD

 

[cableguy47905 via AccessMonster.com]

Thanks Steve for the quick response.

I had actually thought of that and I have the check box, and a psuedo if
statement.

The problem I am having would be with the second append query. Since it has
data from both the form and from another query I am not sure how the SQL will
look.

I had even thought about maybe a "make table" query with the values from the
form and the values from the query, then append the results of that to the
LocationContact table.

I am pretty stumped on this one.

I appreciate any help that I can get with this.

Thanks,

Here's one way --- have two append queries,
1) Data from the form.
2) Data from the form AND the data from the Location table.

At the point where the trigger (button) that runs the query, in the have an
IF statment in the Event Procedure that will select the correct query based
upon the value of the checkbox.

Something like:
'comment - append data based upon checkbox
If me.chkAllLocs = True then
docmd.runquery "qry_all locations"
Else
docmd.openquery "qry_formlocation"
End if

See if that works for you.

SteveD

There are many postings dealing with append queries and they have helped a
great deal with the other one I was working on, but this one is different.

[quoted text clipped - 22 lines]

Is this possible with my train of thought right now?

 

[SteveD]

Here's the SQL for what your trying to do. you should be able to copy this
and then open a new query, switch to SQL mode, and paste it in, then switch
to Design mode. You don't have the table I used in the example, but you
should be able to "see" how it works. This query uses the Zip from a table
and the City and State from the form.

INSERT INTO tbl_NewTable ( ZIP, City, State )
SELECT Zipcodes.ZIP, [forms]![frm_NewCityStateLetters]![txtCity] AS City,
[forms]![frm_NewCityStateLetters]![txtState] AS State
FROM Zipcodes
WHERE (((Zipcodes.ZIP)=[forms]![frm_NewCityStateLetters]![zip]));

Let me know if you need more.

SteveD

Thanks Steve for the quick response.

I had actually thought of that and I have the check box, and a psuedo if
statement.

The problem I am having would be with the second append query. Since it has
data from both the form and from another query I am not sure how the SQL will
look.

I had even thought about maybe a "make table" query with the values from the
form and the values from the query, then append the results of that to the
LocationContact table.

I am pretty stumped on this one.

I appreciate any help that I can get with this.

Thanks,

Here's one way --- have two append queries,
1) Data from the form.
2) Data from the form AND the data from the Location table.

At the point where the trigger (button) that runs the query, in the have an
IF statment in the Event Procedure that will select the correct query based
upon the value of the checkbox.

Something like:
'comment - append data based upon checkbox
If me.chkAllLocs = True then
docmd.runquery "qry_all locations"
Else
docmd.openquery "qry_formlocation"
End if

See if that works for you.

SteveD

There are many postings dealing with append queries and they have helped a
great deal with the other one I was working on, but this one is different.

[quoted text clipped - 22 lines]

Is this possible with my train of thought right now?

 

[cableguy47905 via AccessMonster.com]

I finally got it. It is probably the long way around, but so far it sort of
works. I have to work out a bug.

1. Each time the Make Table query runs it tells me it will delete the
current table. I need to get that to not pop up and just delete the table.

Any Ideas with that?

Below is the code I used to get this to work.



Private Sub chkAllLocs_AfterUpdate()
Dim strSQL As String
Dim strDocName As String

strDocName = "QRY_contactLocationTempMT"
If Me.chkAllLocs.Value = -1 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenQuery strDocName

End If


End Sub

---------------------------------
Private Sub cmdSaveRec_Click()
On Error GoTo Err_cmdSaveRec_Click

Dim strSQL As String
Dim strSQL2 As String

If Me.chkAllLocs.Value = 0 Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

strSQL = "INSERT INTO TBL_LocationContact (CompanyID, LocationID,
ContactID) VALUES (" & Me![txtCompanyID] & "," & Me![txtLocationID] & "," &
Me![txtContactID] & ");"
Debug.Print strSQL
DoCmd.RunSQL strSQL
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

strSQL = "UPDATE TBL_ContactLocationTemp SET TBL_ContactLocationTemp.
ContactID = Forms!FRM_Contacts!txtContactID WHERE (((TBL_ContactLocationTemp.
ContactID)=0));"
Debug.Print strSQL
DoCmd.RunSQL strSQL

strSQL2 = "INSERT INTO TBL_LocationContact ( CompanyID, LocationID,
ContactID ) SELECT TBL_ContactLocationTemp.CompanyID, TBL_ContactLocationTemp.
LocationID, TBL_ContactLocationTemp.ContactID FROM TBL_ContactLocationTemp;"
Debug.Print strSQL2
DoCmd.RunSQL strSQL2

End If


Exit_cmdSaveRec_Click:
Exit Sub

Err_cmdSaveRec_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRec_Click

End Sub

 

[SteveD]

The warning message can be dealt with a set of commands
before the update add:
DoCmd.SetWarning False

Then after the query or queries run, then add:
DoCmd.SetWarnings True

It's important to have both, else you'll never have any warnings.

SteveD