Convert Query to DoCmd.RunSQL

[Learn2010]

I am trying to experiment a little to get more familiar with the SQL concept. I run this query and it works. I copied the SQL version of it and tried to adapt it to use the DoCmd.RunSQL command. It fails to work. Can you point me in the right direction?

Here is the original SQL from the query:

START OF ORIGINAL
UPDATE tblREFERREDTOHOLD INNER JOIN tblREFERREDTOCONTACTS ON tblREFERREDTOHOLD.ReferredToContactID = tblREFERREDTOCONTACTS.RefToContactID SET tblREFERREDTOHOLD.ReferredToTitle = [tblREFERREDTOCONTACTS].[RefToTitle], tblREFERREDTOHOLD.ReferredToFirstName = [tblREFERREDTOCONTACTS].[RefToFirstName], tblREFERREDTOHOLD.ReferredToMI = [tblREFERREDTOCONTACTS].[RefToMI], tblREFERREDTOHOLD.ReferredToLastName = [tblREFERREDTOCONTACTS].[RefToLastName], tblREFERREDTOHOLD.ReferredToSuffix = [tblREFERREDTOCONTACTS].[RefToSuffix], tblREFERREDTOHOLD.ReferredToAddress1 = [tblREFERREDTOCONTACTS].[RefToAddress1], tblREFERREDTOHOLD.ReferredToAddress2 = [tblREFERREDTOCONTACTS].[RefToAddress2], tblREFERREDTOHOLD.ReferredToCity = [tblREFERREDTOCONTACTS].[RefToCity], tblREFERREDTOHOLD.ReferredToState = [tblREFERREDTOCONTACTS].[RefToState], tblREFERREDTOHOLD.ReferredToZipCode = [tblREFERREDTOCONTACTS].[RefToZipCode], tblREFERREDTOHOLD.ReferredToCounty = [tblREFERREDTOCONTACTS].[RefToCounty], tblREFERREDTOHOLD.ReferredToPhone = [tblREFERREDTOCONTACTS].[RefToPhone], tblREFERREDTOHOLD.ReferredToPhoneExt = [tblREFERREDTOCONTACTS].[RefToPhoneExt], tblREFERREDTOHOLD.ReferredToPhone2 = [tblREFERREDTOCONTACTS].[RefToPhone2], tblREFERREDTOHOLD.ReferredToPhone2Ext = [tblREFERREDTOCONTACTS].[RefToPhone2Ext], tblREFERREDTOHOLD.ReferredToCell = [tblREFERREDTOCONTACTS].[RefToCell], tblREFERREDTOHOLD.ReferredToFax = [tblREFERREDTOCONTACTS].[RefToFax], tblREFERREDTOHOLD.ReferredToEmail = [tblREFERREDTOCONTACTS].[RefToEmail];
END OF ORIGINAL

Here is my converted Do.cmd.RunSQL statement:

START OF CONVERTED STATEMENT
"UPDATE tblREFERREDTOHOLD INNER JOIN tblREFERREDTOCONTACTS ON tblREFERREDTOHOLD.ReferredToContactID = tblREFERREDTOCONTACTS.RefToContactID " & _
"SET tblREFERREDTOHOLD.ReferredToTitle = [tblREFERREDTOCONTACTS].[RefToTitle], tblREFERREDTOHOLD.ReferredToFirstName = [tblREFERREDTOCONTACTS].[RefToFirstName], " & _
"tblREFERREDTOHOLD.ReferredToMI = [tblREFERREDTOCONTACTS].[RefToMI], tblREFERREDTOHOLD.ReferredToLastName = [tblREFERREDTOCONTACTS].[RefToLastName], " & _
"tblREFERREDTOHOLD.ReferredToSuffix = [tblREFERREDTOCONTACTS].[RefToSuffix], tblREFERREDTOHOLD.ReferredToAddress1 = [tblREFERREDTOCONTACTS].[RefToAddress1], " & _
"tblREFERREDTOHOLD.ReferredToAddress2 = [tblREFERREDTOCONTACTS].[RefToAddress2], tblREFERREDTOHOLD.ReferredToCity = [tblREFERREDTOCONTACTS].[RefToCity], " & _
"tblREFERREDTOHOLD.ReferredToState = [tblREFERREDTOCONTACTS].[RefToState], tblREFERREDTOHOLD.ReferredToZipCode = [tblREFERREDTOCONTACTS].[RefToZipCode], " & _
"tblREFERREDTOHOLD.ReferredToCounty = [tblREFERREDTOCONTACTS].[RefToCounty], tblREFERREDTOHOLD.ReferredToPhone = [tblREFERREDTOCONTACTS].[RefToPhone], " & _
"tblREFERREDTOHOLD.ReferredToPhoneExt = [tblREFERREDTOCONTACTS].[RefToPhoneExt], tblREFERREDTOHOLD.ReferredToPhone2 = [tblREFERREDTOCONTACTS].[RefToPhone2], " & _
"tblREFERREDTOHOLD.ReferredToPhone2Ext = [tblREFERREDTOCONTACTS].[RefToPhone2Ext], tblREFERREDTOHOLD.ReferredToCell = [tblREFERREDTOCONTACTS].[RefToCell], " & _
"tblREFERREDTOHOLD.ReferredToFax = [tblREFERREDTOCONTACTS].[RefToFax], tblREFERREDTOHOLD.ReferredToEmail = [tblREFERREDTOCONTACTS].[RefToEmail]"
END OF CONVERTED STATEMENT

Thanks for any help.

 

[RainLover]

The first thing you need to learn is that ALL CAAPITOLS is very hard to read.

Next is that it is better not to use DoCmd.RunSQL

Instead use CurrentDB.Execute.

I am sure someone with better eyes could help. Sorry that I can't.

 

[CJ_London]

I agree - awful to try and read!

just a note before I ramble on you have put

Here is my converted Do.cmd.RunSQL statement

- this should be Docmd.RunSQL.

If this is not the reason then read on..

Why did it fail to work? did it generate an error? In which case what was the error?

In my humble opinion, the only benefit of docmd.runsql is it prompts the user (and generates error messages) in the same way as running the query directly - perhaps fine for debugging but long term better to use currentdb.execute which has other benefits including reporting the number of records affected (i.e. 20 records updated using the recordsupdated property).

but turning back to the question, you are probably missing a space somewhere.

Suggest create a variable called StrSQL

then in your code simply copy and paste the sql from your query to assign it to strSQL, then use docmd.runsql(strSQL)

Once you have confirmed it is running OK, then you can start to make the code more readable using " &_ (or I prefer strSQL=strSQL & "...)

 

[Learn2010]

I had the DoCmd.RunSQL. I now have this:

Start of Code
Dim StrSQL As String

StrSQL = "UPDATE ---rest of the code----"

DoCmd.RunSQL StrSQL
End Of Code

I am using a combo box based on a Select Query. After I make my selection in the combo box (the code is in the After Update field), it displays the first two fields in the combo box and nothing else. This happened both ways that I tried it. Any Ideas?

 

[jdraw]

For a combo the rowsource is most likely a SELECT query.

You are showing us an UPDATE query.

Please explain.

 

[Learn2010]

The query behind the combo box is a Select query. The SQL statement is an Event Procedure in the After Update of the combo box. After the selection is made in the combo box, the Update then runs.

 

[Learn2010]

I simplified the query for editing sake. Can you fix this?

Start of Code
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblREFERREDTOHOLD INNER JOIN tblREFERREDTOCONTACTS ON tblREFERREDTOHOLD.ReferredToContactID = tblREFERREDTOCONTACTS.RefToContactID " & _
"SET tblREFERREDTOHOLD.ReferredToFirstName = [tblREFERREDTOCONTACTS].[RefToFirstName], tblREFERREDTOHOLD.ReferredToMI = [tblREFERREDTOCONTACTS].[RefToMI], " & _
"tblREFERREDTOHOLD.ReferredToLastName = [tblREFERREDTOCONTACTS].[RefToLastName], tblREFERREDTOHOLD.ReferredToSuffix = [tblREFERREDTOCONTACTS].[RefToSuffix]"
End of Code

 

[jdraw]

Where do you reference the combo?

 

[CJ_London]

Is the update query supposed to interact with the combo box in some way?

If so, your sql needs to include criteria which references the combo box

i.e "WHERE SomeValue = " & combobox

I simplified the query for editing sake. Can you fix this?

What's to fix? did you try what I suggested? and did it work before you started to make the code more readable? Your response was not clear.

then in your code simply copy and paste the sql from your query to assign it to strSQL, then use docmd.runsql(strSQL)

Once you have confirmed it is running OK, then you can start to make the code more readable using " &_ (or I prefer strSQL=strSQL & "...)

 

[Learn2010]

I will try to clear it up.

I have a form with tblReferredToHold as the recordsource. There are 2 comboboxes on the form.

Combobox 1 is to select an agency. The controlsource is the field ReferredToAgencyID. When an agency is selected. the field retains a number and displays the agency name.

Combobox 2 is to select all the contacts from the selected agency. The rowsource for this combobox is the select query qryReferredToContact, which has an inner join based on ReferredToAgencyID and the corresponding field RefToAgencyID in tblReferredToContacts.

When an agency is selected in Combobox 1, Combobox 2 is refreshed, resulting in displaying only contacts from that agency.

When the contact is selected in Combobox 2, the event procedure in the after update event option behind it runs. This should populate tblReferredToHold with the corresponding fields from tblReferredToContacts.

Clear as mud, I know.

Thank you.

 

[CJ_London]

This should populate tblReferredToHold with the corresponding fields from tblReferredToContacts.

I have a form with tblReferredToHold as the recordsource

If I have understood you correctly, why do you need to run the query? The query is updating the fields in the recordsource to your form and they will automatically update when you close the form, move to a new record etc. anyway

If you want to make sure the record is updated when combo2 is completed then in the after update event of combo 2 you just need

docmd.save

Going back to your original query - why do you copy the data from the contacts table - wouldn't it be simpler just to link the two tables on contactID?

Your data structure may be clear to you, but it does seem a bit confusing to me

 

[Learn2010]

Well, back to the original question. Is there anything wrong with the expression itself?

 

[jdraw]

Let's go back one step further, what exactly are you wanting to do in plain English.

The Update implied an update to an existing record(s) using a value from the combo. But you don't reference the combo.

So for clarity, what are you trying to do? There may be several options.

 

[RainLover]

If your design was correct there would be no need to run any code what so ever.

 

[Learn2010]

Let me study this some more. I need to clarify it for myself. Thanks for all your help.

 

[CJ_London]

one other thing to consider - if you try to update a record which is already the focus of the form, the chances are it is locked so an update query will fail