Correct Syntax To Write Command Line Able To UPDATE More Than One Field In Table
Nov 24, 2014
what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.
Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:
TableI
A B C D
1 2
2 6 4 3
1 7 5 9
1 2
2 5 8 5
etc.
I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:
TableI
A B C D
1 2 & %
2 6 4 3
1 7 5 9
1 2 & %
2 5 8 5
I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:
Private Sub BtSalvarFrmII_Click()
CurrentDb.execute "UPDATE TableI"
Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2
Docmd.save
Docmd.close
End Sub
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I'm looking to create a "Same As Billing Address" check box that would automatically udpate the address in an order to reflect the customer's billing address.
I understand how to sync a combo box to an option group ( -- great article by the way)
I Guess what I'm looking to do is:
1. Sync a "Same As" Checkbox to multiple text boxes in the form (Unit, StreetAddress, City, Province, Postal) such that these fields are UPDATED (writen to) with the same information as the billing address
2. [U]IF the "Same As" Option is selected, I need the updated fields to lock, allowing no further update... but must by unlocked if the "Same As" option is NOT selected.
Please help me out with this one guys.. I really need it.
This is what I'm trying to fit into the control box. Maybe there is a much better way to do it so suggestions are very welcome. I don't know if I can even use "!" in the IIF statement. And I think it's too long, so it's invalid.
What I'm trying to do is to check if all those fields are passing (or NOT failing). Then insert "Yes" or "No" into a textbox.
IIF(blackStartPlans!=”fail” and operatorTraining!=”fail” and telemetryAvailable!=”fail” and backupControlCenter!=”fail” and vegetationManagement!=”fail” and capacityEmergencies!=”fail” and cat_A_contingency!=”fail” and cat_B_contingency!=”fail” and cat_C_contingency!=”fail” and cat_D_contingency!=”fail” and electricityFacRating!=”fail” and relayMaintenance!=”fail” and UFLS!=”fail” and UVLS!=”fail” and Other!=”fail”, “Yes”, “No”)
I have a memo field that contains several text strings separated by line breaks. I would like to write a query to add another text string to the end of the field. How do I embed a line feed in the SQL?
I have a form which has an unbound text field which is a calculated filed showing a date which is x months after a separate field on the form.
I need to write this field back to a table in the database when an "add" command button is clicked.
The table is called Assets The form is called Assets List The text field on the form is called txtWED and needs to be written back to the table "Assets" in the field "Warranty_End_Date"
If possible can I add this as an expression on an "add" command button?
I have a query with many fields. One of them is the combination of two fields and is called "Components Reference". What I try to do is, every time this query is updated, the records of only this field "Components Reference" to be written to the field of another table which also takes records from an another query. I wish I was clear enough.
I want to be able to update a date field in my form with a command button based off the date the person picks off a combo box in my header... is this possible?
I am setting up a "to_do" list in access. One of the field is the date the task was last completed. I want a button to update that date to the current date for the record that i am viewing on the form. Below is the code that I am using. When I click on my button, it updates the first record in my to-do table but not the current record displayed in my form.
Private Sub Cmd_Update_Click() Dim DB As Database Dim rs As Recordset Dim i As Long On Error GoTo Command4_Click_Error Set DB = CurrentDb
i want to open a Word Document in access, so i am using the Runapp macro. It says to enter a command line. So, i input the path of the document and run the macro. Then access says it cannot invoke the application.
what is the command line?
in general, how do i open a word document in access using the Runapp macro?
I am at my Login Screen, I want it to return to the Login Screen if you select "NO" and Close the DB if "YES"
Here is my current code:
Private Sub Form_Close() If MsgBox("Would you like to EXIT the Database?", vbYesNo, "Quiting Database") = vbYes Then Application.Quit Else ??? End If End Sub
Guys, any idea how to evaluate the command line options used when an Access application is started from a command line. For example I use the following command line: C:..MyDatabase.mdb /parameters:110001110 Then, in VB, I evaluate the string "110001110" and do the further job. Is there any property or function to return the command line option string? Thanks
I am trying to run the batch job from the command line. I created AutoExec macro in the mdb(msaccess 2007) which is calling someMainMethod() . How can I pass batch job id to the mdb?
I have been trying to find the symbol or text to force a new line on a command button in a Access Form and have not had any luck. I have the command button pretty large and want 2 lines in the button. If anyone knows how to do this that would be great. Thank you very much !!!
This may not be an Access problem per se, and if not, I appologize in advance.
I have a FE/BE split database with multiple users. I have found out how to add a command line switch to the shortcuts (the shortcut must go to the application first for any switches to work) which opens the database front end nicely. Problem is, I can't get it to connect to the appropriate WIF.
I have tried copying the text from "Microsoft Access Inside Out" which didn't work as it has "/wrkgroup" rather than "/wrkgrp". My set up wouldn't accept that.
Anyway I'm half way there but stuck now. I don't get any error messages when I use the shortcuts but Access stays joined to whatever WIF it was connected to the last time it was running.
I have searched this site but couldn't find anything regarding this specific problem, in the recent past at least.
Any ideas what I'm doing wrong?
I have copied the details from the 2 shortcuts below. The first is to my database and the networked WIF, the second to Access to return the desktop PC to the default WIF. I have also added the Access version number and operating system version numbers.
This is the shortcut to my database with the workgroup switch set to the WIF created for this database:
Is there a command line argument to open AND close a db? I am running a schedule task to open a db which has an autoexec macro but I don't want the db to stay open after running the macro. Or is there a close db function in a macro? I see close form but not db.
I am looking for help to to generate a command line to automatically run a report within a windows program I am using. The program I use has command line fields to automatically open the access report when I complete my program session. I have tested the command line with a standard C:.... .exe to open Word, Access, etc.
I have tried using the create shortcut and snapshot methods without success and wondering if a macro is needed (havent a clue).
Can I use the Like command in an If Then statement? I'm getting a syntax error
If [Primary1Name] Like 'PO*' Then MsgBox "You cannot use a post office box address. Use a residence address.", vbOKOnly, "Incorrect Address Information" DoCmd.GoToControl "Primary1Address1" Else End If
I am working on supporting spaces in the DB filename. I got the rest of my program working... other than being able to invoke Access with the specified filename for it to open.
Code: C:Documents and Settingsc_mlueckMy DocumentsFandango>"C:Program FilesMicrosoft OfficeOffice12MSACCESS.EXE" "Schema Ideas.accdb"
C:Documents and Settingsc_mlueckMy DocumentsFandango>dir "Schema Ideas.accdb" Volume in drive C has no label. Volume Serial Number is C8E4-5856
Directory of C:Documents and Settingsc_mlueckMy DocumentsFandango
I have had my 4th SQL/APP Dev lab class, however I missed the 5th.
Our 5th lab was .
1. Create 3 tables, populate with given data and create relationships. 'Customer, Invoice, Payment'
2. We were given 8 queries to try do, we had to come up with which commands would be used.
I have no 1 completed.
As for no 2.. we are still learning all the commands, so the lab here is for us to find what commands we think will be used. I have researched and have them all 6 queries we have to run.., I have all those commands written out on paper which I think are answers..how to find the command line to test them.