Update Query with a counter

[hockeyman9474]

I have a form that uses input boxes to ask for a Street Name, Zip, and Development. I need to update a table called Clients. Where the Street Name and Zip = what the user input, the development field will be updated with what the user inputs. I know to use an execute command but I have spent all day trying to understand them and I am so lost in user defined and type mismatch errors. Can someone please help with this code and please explain as best as possible so i understand it and not just copy it. Also, DAO vs ADO....a little lost in that as well .PLease explain.

[June7]

Help with what code? You have not provided anything for us to analyze.

I do not know what you mean by 'update query with a counter'.

What are you trying to do?

http://allenbrowne.com/ser-29.html

[rpeare]

a user defined and type mismatch is typically when you are trying to perform a mathematical function on a text string or using a text criteria for a numeric field

for instance if your zip code is stored as a string (and typically it is) and you had a criteria of 12345 you would get a criteria mismatch. If your criteria is, instead '12345' or "12345" you would not get the error.

So let's say on your form named SEARCHFORM and you are typing in part of a street name, the zip code (assuming this is a text value and not numeric) and a development field. You are trying to update the development field in your table only.
Further, let's say on your form your address search field is named ADDRESSSEARCH and your zip code search field is named ZIPSEARCH and your development update field is DEVUPDATE.

Your criteria on your ADDRESS field in your query design window would probably be

LIKE "*" & [forms]![SEARCHFORM]![ADDRESSSEARCH] & "*"

NOTE: Your addresses are probably stored something like 1313 Mockingbird Lane. You would use LIKE if you were selecting the street name because it's a sub part of the string so assuming you had two streets named MOCKINGBIRD and MOCKINGJAY your ADDRESSSEARCH field would have to be MOCKINGJ or MOCKINGB to differentiate between the two streets.

Your zip code search would be the same unless you require the zip code to be exact, if you do your criteria on the zip code field would be

[forms]![SEARCHFORM]![ZIPSEARCH]

Finally, your UPDATE TO value on your DEVELOPMENT field would be

[forms]![SEARCHFORM]![DEVUPDATE]

Personally I have noticed very little difference between DAO and ADO, the syntax is similar I just prefer DAO because it's part of the default settings when you create a new database.

[hockeyman9474]

Thank you. very intuitive. I did know this information, but in a vague sense. I work mostly with SQL now but it's similar with the LIKE code. In response to the code, I guess I am looking for the right code rather to have some analyzed. From my research I know that what I need to do is to use an Execute statement with a SQL Update code. I have seen it done it different ways some with dao some with ado sometimes a function is called, sometimes it's coded right in the click property of a button. I'm looking for some direction mostly. Here's what I have a form with a button that says update development when clicked 3 inputboxes ask for street name, zip and development that can be store in text boxes that are hidden or variables. I then take the street name and zip and check against a table and if conditions are met then update development column with the third inputbox entry. I check both street and zip because you can have main street in your db more than once in two different towns(Zips). When the update is done I'd like to display the number of records updated. A small example of what I am thinking, but i think i would need to build on it:

Dim db As DAO.Database


Set db = CurrentDb
db.Execute "UPDATE mytable SET myfield=22"

msgbox db.RecordsAffected

[rpeare]

You'd have to build your SQL statement in the code then

something like

dim db as database
dim sSQL as string

set db = currentdb
'If you are updating a number field, remove the single quote (') from the statement
sSQL = "UPDATE mytable SET Myfield = '" & [forms]![formname]![fieldname] & "' WHERE (AddressField) like '*" & [forms]![formname]![addresssearchfield] & "*' AND (ZipField) = '" & [forms]![formname]![zipsearchfield] & "'"
db.execute ssql
msgbox db.recordsaffected

you can debug.print your sSQL string before you ever try to run it with the db.execute command to see if it's giving you a valid statement, you can just cut and paste the statement into a query window and see if it runs correctly.

[hockeyman9474]

1. As far as the address field goes why have what they type in preceded or followed by anything else? For example if they want to update Peter Rd. and they type St. Peter Rd. and there really is a St. Peter Rd but I didn't want St. Peter Rd updated.....or am I reaching here? Or is this because Address's are generally preceded by a number? I just thought maybe I will have a field for the street number and one for the street, but this seems to make more sense.
2. Do I need to include the () around the fields names?
3. Will this work with Variables. i.e. Whatever they type for address for into a variable called AddressVar. can I type WHERE (AddressField) like '*" & [forms]![Formname]![AddressVar] & "*' instead?
4. One last question. Please explain why the double quotes are needed.

[hockeyman9474]

You know what can you explain the entire update line and translate. I just want to understand what I'm doing rather than copying code. I appreciate your help. Thank You.

[rpeare]

you're not really copying code, you still have to adapt it to your situation/field names.

1. Yes, you need the * in front because you have a street numbers will not give you a result. If you want to match exactly you would have to parse out the street name (basically everything after the first space in most cases but not all). The wild card in the back of the string is just my example, you can remove them entirely if you don't want them.
2. I included the () around the field names because access will put those into the SQL string anyway, they are not required, however, if you have special characters in your field names like # or spaces or any mark that is not an underscore (_) you will be required to put square brackets around them [].
3. If you want to type in the street name at the time the query is run and not retrieve it from a form you would do something more like:

WHERE (AddressField) like '*" & [Enter the Partial Address] & "*'

4. Why are double quotes needed where? If you're talking about in the where clause in particular the " marks in string building indicate the beginning and end of a string, so what I'm doing is substituting in a variable between two different strings.

[hockeyman9474]

i still have questions about this but i don't to keep bothering you. Can you suggest a good webpage that will explain all of this/

[hockeyman9474]

I thought ' represented a string which is why I was lost.

[rpeare]

The board is here for questions :P


Either ' or " can represent a string in MS Access (queries).

In other words

SELECT * FROM TableName WHERE (TextField) = 'TEST'
is identical to
SELECT * FROM TableName WHERE (TextField) = "TEST"

The reason you would want to use ' marks to indicate text when building a SQL string is that ' and " have entirely different meanings when programming in VBA.

if I were to build these two strings in VBA I would have

sSQL = "SELECT * FROM TableName WHERE (Textfield) = 'TEST'"
or
sSQL = "SELECT * FROM TableName WHERE (TextField) = ""TEST"""

In other words if you want a double quote to appear within a string as a character you need to place two of them together to indicate a single, double quote in the actual string. I often lose track of trying to maintain double quotes in strings so I try to stick to the single quote mark to indicate text within a string.

[orange]

Here's another bit of code showing the use of RecordsAffected using DAO. But it isn't clear exactly what you are having difficulty with (to me at least).

'---------------------------------------------------------------------------------------
' Procedure : UpdateBy
' Author    : Jack
' Date      : 25-10-2011
' Purpose   :  Update records in a table(Invoicetest) showing who (Environ("UserName")) imported the record.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Public Sub UpdateBy()
          Dim SQL As String
          Dim db As DAO.Database
          Dim WINUSERNAME As String
10        Set db = CurrentDb
20       On Error GoTo UpdateBy_Error

30        WINUSERNAME = Environ("UserName")
          
40        SQL = "UPDATE Invoicetest SET ImportedBy='" & WINUSERNAME & "';"
          
          'Execute the Update sql and put up a msgbox showing How ManyRecords were Updated
50       db.Execute SQL, dbFailOnError
51       MsgBox "Records updated " & db.RecordsAffected, vbOKOnly
60       On Error GoTo 0
70       Exit Sub

UpdateBy_Error:

80        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure UpdateBy of Module AWF_Related"
End Sub

Good luck with your project.

[hockeyman9474]

OK so ' ' indicates a string is in between them and " " indicates a variable is in between them? So in a very plain visualization:

' "[forms]![MyForm]![Fieldname]" '

is a variable in a sql string in vba....more or less. Right?

the * means exact match right? I am still a bit confused with how you explained it above. Does using the* eliminate any preceding number or just one character? I wasn't sure what you meant by parse. The user will always type a street name but the the table will always contain a number and a street name.

and what does the & represent?

[June7]

Parse means to break apart or extract from.

The number and street name are combined in field value and the search needs to match only the name part.

The * is a wildcard character and means to match any and all characters encountered before and/or after the given literal text. So "*smith*" will match and return:

John Smith
John Q. Smith
John Smith Jones

About wildcards http://office.microsoft.com/en-us/ac...005188185.aspx

Your visualization of variable and SQL is not quite there. The variable is not within the SQL. When code runs and the SQL is compiled, it is the value within the variable that becomes part of the SQL string. Say the value held by [fieldname] is Smith. Then the code:

sSQL = "SELECT * FROM TableName WHERE (Textfield) = '" & [forms]![MyForm]![Fieldname] & "';"

Will compile to:

SELECT * FROM TableName WHERE (Textfield) = 'Smith';

[rpeare]

you need the ampersands to indicate you're continuing to build a statement that is a mix of string/variable so if you are taking an excerpt of a line it would be


'(indicates beginning of string in SQL statement)"(closes string of VBA code) & (continue building string) [forms]![myform]![fieldname] (field on a form) & (continue string) " (open new VBA string) ' (indicates close of string in SQL statement)

(beginning of expression)...'" & [forms]![myform]![fieldname] & "'.... (end of expression)

The star is a wildcard used with LIKE on text values to find anything that matches so

like 'TEST*' would find values in your field that BEGINS with the string TEST
like '*TEST' would find values in your field that END with the string TEST
like '*TEST*' would find values in your field where TEST appears ANYWHERE within the string.

Parsing means separating a single value into multiple values

For instance if you have the address

1313 Mockingbird Lane

and you just want to type in 'mockingbird lane' you would have to parse the string like

trim(right([Fieldname], len([Fieldname])-instr([Fieldname], " ")))

this would give you just the "MOCKINGBIRD LANE"

but, for instance if you have

1313 Mockingbird Lane
1314 Mockingbird Ln
1315 Mockinbird Lane

you will not be able to capture all three in a single query because the street name would be different for all three.

Further if you had

1316 A Mockingbird Lane you wouldn't 'match' either because the formula would give you the string 'A Mockingbird Lane' if you were trying to match EXACTLY on the street name. That's why I suggested using the LIKE operator, it just sort of jeopardizes other data for instance if you have

1212 Water St
3000 Waterfall St

if you were using the LIKE operator here and just typed in WATER it would find both.

Dealing with strings is notoriously difficult when trying to find similar items, it's why google makes big bucks figuring out what you mean from what you type.