Solved

Handling Queries in VBA Code

Posted on 2004-09-15
9
235 Views
Last Modified: 2006-11-17
Hi, can anyone help me please?

I need some code that will enable me to modify two fields of certain multiple records in a table. The trigger will be from the yes button of a msgbox.

My first idea was to build a query to select the records, but then I don't know how to update the values in the query. Also, how can I dynamically send criteria to the query from the code?

Thanks in advance!
0
Comment
Question by:PJRimmer
9 Comments
 
LVL 26

Expert Comment

by:dannywareham
ID: 12063224
You need to use an UPDATE query.

If you can give examples of your table name, fields required and when the values would be changed, we can show you how to build the queryand incorporate into VBA
0
 

Author Comment

by:PJRimmer
ID: 12063247
I've done a bit more work on this and I am trying to do the following:

DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = True, tbl_VantageCharging.EndDate = Now()" & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = False);"

Seems to be almost there, but it is complaining about a data type mismatch in the criteria. Any ideas? Am I on the right track?
0
 

Author Comment

by:PJRimmer
ID: 12063271
Sorry, missed a couple of lines. It's all part of a msgbox!

        If MsgBox("Do you want to end all current charges and start a new charge(s) for the new package?", vbYesNo + vbQuestion, "Change Charges?") = vbYes Then
            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = True, tbl_VantageCharging.EndDate = Now()" & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = False);"
        Else
        End If

Cheers!
0
 
LVL 26

Expert Comment

by:dannywareham
ID: 12063274
You've answered your own question!

As for teh data-type mismatch, this will be caused because the field "Ended?" or "EndDate" in table "tbl_VantageCharging" will only accept certain types of values (for example, a number field will not accept text).

Check teh type of data being added (change TRUE to -1 for example)
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:PJRimmer
ID: 12063440
Hey I was a bit hasty asking the question!

I have been messing around trying different things, but I sill get data type mismatch.

            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = '-1', tbl_VantageCharging.EndDate = '" & Date & "' " & _
                "WHERE (tbl_VantageCharging.AccountNo = " & AccountNo & " AND tbl_VantageCharging.[Ended?] = '0');"

Any advice of use of brackets, '&' and quote marks?
0
 
LVL 2

Expert Comment

by:Hakim
ID: 12063485
my 2 cents!

There is no need of inclosing -1 and 0.
0
 

Author Comment

by:PJRimmer
ID: 12063692
Yeah I tried that but I still get the same error:

Run-time error '3464':

Data type mismatch in criteria expression.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 250 total points
ID: 12063732
If any of the fields are text data types, you need to enclose the values that you are updating to with ' characters. If they are numerics, you don't, and dates use the # delimiter. In this example below, I'm assuming Ended is a numeric (boolean) field, EndDate is a Date/Time field and AccountNo is a text field:

            DoCmd.RunSQL "UPDATE tbl_VantageCharging " & _
                "SET tbl_VantageCharging.[Ended?] = -1, tbl_VantageCharging.EndDate = #" & Format(Date,"mm/dd/yyyy") & "# " & _
                "WHERE (tbl_VantageCharging.AccountNo = '" & AccountNo & "' AND tbl_VantageCharging.[Ended?] = 0);"
0
 

Author Comment

by:PJRimmer
ID: 12064348
Thanks, that all works fine now!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now