update query not working

[mafhobb]

I have a table with the following fields ID, Device, Doc, Computer. All strings except for ID.

This table would typically look something like this:

1 Epson A4 DellLaptop
2 Canon Small Label DellLaptop
3 Canon LargeLabel DellLaptop
4 Epson A4 Toshiba
5 Brother SmallLabel Toshiba
6

What I am trying to do it to collect the name of the computer, the document selected and the size of the document and through a query, update the table in this way:

Let's say that I go through the code and my variables are Device=Canon, Document=A4 Computer=DellLaptop

The query needs to go to the table and see if the computer "DellLaptop" has any records in the table. In this case it has three. Then see if any of the records have a matching document, which happens once. Then change the printer in the 1st record to Canon so the table would look like this:

1 Canon A4 DellLaptop
2 Canon Small Label DellLaptop
3 Canon LargeLabel DellLaptop
4 Epson A4 Toshiba
5 Brother SmallLabel Toshiba
6

If the computer does not have any records at all (for example, Computer=DellStation Device=Epson Document=SmallLabel) then an entirely new record is created inputing the three variables.

1 Canon A4 DellLaptop
2 Canon Small Label DellLaptop
3 Canon LargeLabel DellLaptop
4 Epson A4 Toshiba
5 Brother SmallLabel Toshiba
6 Epson SmallLabel DellStation

I am aiming to have each printer that each computer uses listed only once alongside the size of the document printed.

I was hoping to do this with the following query:

    InsertingData = "UPDATE tblPrinterSelection SET Device='" & PrintSel & "', Computer='" & sHostName & "' WHERE Doc='" & DocType & "';"
    DoCmd.RunSQL InsertingData

But this query does not insert any data in the table unless the WHERE condition is met, which means that it does not work for new computers and/or computers which do not have all the document sizes already in the table. In other words, this query UPDATES, but it doe snot enter NEW information. How can I make that happen?

What am I missing?

mafhobb

 

[pbaldy]

An UPDATE query edits existing records, it doesn't create new ones. One thought that comes to mind is using a DCount() to test the table for the WHERE condition being met. If the count is 0, run an append query to create a new record, otherwise run your UPDATE query.

 

[mafhobb]

Thank you Paul. You were absolutely correct. This is how I did it, after your comment

    If DCount("ID", "tblPrinterSelection", "Computer='" & sHostName & "'" & "And Doc='" & DocType & "'") = 0 Then
        'If no matches then do an Append query
        CurrentDb.Execute "Insert Into [tblPrinterSelection] (Computer,Doc,Device) VALUES ('" & sHostName & "','" & DocType & "','" & PrintSel & "')", dbFailOnError
    Else
        'If match then do an Update query
        InsertingData = "UPDATE tblPrinterSelection SET Device='" & PrintSel & "', Computer='" & sHostName & "' WHERE Doc='" & DocType & "';"
        DoCmd.RunSQL InsertingData
    End If

mafhobb

 

[v4let20]

Thank you Paul. You were absolutely correct. This is how I did it, after your comment

    If DCount("ID", "tblPrinterSelection", "Computer='" & sHostName & "'" & "And Doc='" & DocType & "'") = 0 Then
        'If no matches then do an Append query
        CurrentDb.Execute "Insert Into [tblPrinterSelection] (Computer,Doc,Device) VALUES ('" & sHostName & "','" & DocType & "','" & PrintSel & "')", dbFailOnError
    Else
        'If match then do an Update query
        InsertingData = "UPDATE tblPrinterSelection SET Device='" & PrintSel & "', Computer='" & sHostName & "' WHERE Doc='" & DocType & "';"
        DoCmd.RunSQL InsertingData
    End If

mafhobb

Heey man, may i ask you why at the first "if" you used "Currentdb.Execute" but at the second one you used "DoCmd.RunSQL" instead?

 

[pbaldy]

Happy to help!

 

[mafhobb]

Well, I thought I had it worked out, but apparently not. I am getting error 3144, Syntax error in update query.

This is the query

        InsertingData = "UPDATE tblPrinterSelection SET [Device]='" & PrintSel & "', WHERE [Computer]='" & sHostName & "' AND [Doc]='" & DocType & "';"

What I am trying to do is update the [Device] field in the table tblPrinterSelection when [Computer] AND [Doc] match certain values. These are all string variables. I have verified the the values PrintSel, sHostName and DocType are valid.

Any ideas?

mafhobb

 

[mafhobb]

In the end, what actually worked is this:

        InsertingData = "UPDATE tblPrinterSelection SET [tblPrinterSelection].[Device]='" & PrintSel & "'" & _
        "WHERE [Computer]='" & sHostName & "' AND [Doc]='" & DocType & "';"

Other than the obvious change in layout, can somebody tell me why the one on the previous post did not work?

mafhobb

 

[vbaInet]

InsertingData = "UPDATE tblPrinterSelection SET [Device]='" & PrintSel & "'[COLOR="Red"][SIZE="4"],[/SIZE][/COLOR] WHERE [Computer]='" & sHostName & "' AND [Doc]='" & DocType & "';"

Error highlighted in red. But mafhobb, I mentioned in one of your threads two days ago that you should be using Debug.Print to print the string to the Immediate Window and you will be able to spot your mistakes. These are basic techniques that all developers must know.

 

[mafhobb]

Thanks vbaInet. I am not that well versed at the whole debug process, but I very much see the usefulness of your suggestion. I'll use it often now.

mafhobb

 

[vbaInet]

Fyi, pbaldy gives a brief on this topic:

http://www.baldyweb.com/ImmediateWindow.htm