Solved: Update table query

[Trevor]

I am having a problem with my update query, what I'm trying to do is update a field CallRefnum in a table VMSU-MLT with the value of the field [Call Number] were the [IDNumber] is equaled to Me.IDNumber,
here is what I have it's not working can someone tell me what Im not doing right?

strsql = "UPDATE VMSU-MLT SET CallRefNum = """ & [Forms]![VMSU-ML]![Call Number] & """ WHERE " _
& " [IDNumber]= Me.[IDNumber]';"

[orange]

I am having a problem with my update query, what I'm trying to do is update a field CallRefnum in a table VMSU-MLT with the value of the field [Call Number] were the [IDNumber] is equaled to Me.IDNumber,
here is what I have it's not working can someone tell me what Im not doing right?

strsql = "UPDATE VMSU-MLT SET CallRefNum = """ & [Forms]![VMSU-ML]![Call Number] & """ WHERE " _
& " [IDNumber]= Me.[IDNumber]';"
Try this:

strsql = "UPDATE VMSU-MLT SET CallRefNum ='" & [Forms]![VMSU-ML]![Call Number] & "' WHERE " _
& " [IDNumber]= '" & Me.[IDNumber] & "';"

NOTE:
double quote " in black
single quote ' in red

[rconverse]

if IDNumber is a number, you shouldn't need any single quotes. You only need that for text.


strsql = "UPDATE VMSU-MLT SET CallRefNum = """ & [Forms]![VMSU-ML]![Call Number] & """ WHERE " _
& " [IDNumber]= Me.[IDNumber];"

HTH
Roger

[orange]

if IDNumber is a number, you shouldn't need any single quotes. You only need that for text.


strsql = "UPDATE VMSU-MLT SET CallRefNum = """ & [Forms]![VMSU-ML]![Call Number] & """ WHERE " _
& " [IDNumber]= Me.[IDNumber];"

HTH
Roger

Roger is quite right. If the IDNumber is numeric then no quotes are required.

[Trevor]

IDNumber is A leter and numbers Ie A123456789

[orange]

IDNumber is A leter and numbers Ie A123456789

Then you will need quotes around the value of IDNumber.

[Trevor]

I set up a test and what u suggested Orange works , thanks, but I realizeed that I can't update a record that doesn't exsist yet. what I have is IDNumber in the for and the call number using same table and fields, is there away of passing that value of call Number to the table vmsu-MLT.Callrefnum with the rest of the record, without using a hidden texbox on the form as a temp holding place for the value?

[Trevor]

I have another question and I can do it with an exaple it based upon my previous question about passing a value to the table with the record, because the record doen't exist in the table yet
This time I'll just use the same table for exaple
if Me.IDNumber = "E11111" I want to pass the value "N/A" to VMSU-MLT.CallRefNum

[orange]

I set up a test and what u suggested Orange works , thanks, but I realizeed that I can't update a record that doesn't exsist yet. what I have is IDNumber in the for and the call number using same table and fields, is there away of passing that value of call Number to the table vmsu-MLT.Callrefnum with the rest of the record, without using a hidden texbox on the form as a temp holding place for the value?

I think you are talking of 2 different conditions:

1 -- Update an existing record (which we have discussed)
2 -- Create a new record; one that does not exist yet.

For (2) you would do an INSERT statement

Format: INSERT INTO TableName (fld1, fld2,....fldn)
VALUES("valueoftextfld1",valueOfNumericfld,"valueOfTextFld2",....)

orange

[Trevor]

Thank you, I'll have to try it out, I just got thrown more work, for me to hurry up and push out, so it might be a few days before I get back to you.

[Trevor]

I'm having trouble w/ insert query, I'm using
Insert Into Table [VMSU-ILT]( "Type")
Values("Call")
and I am recieving an error expected end of statement and highlights my perthises, if the compiler doesn't highlight (Type) it highlghth (call)
I am trying to insert Call into the VMSU-ILT table and in field type
I have even tried w/ StrSQL = infront of my statment with it declared as a string and still same error,
note trying to pass value to table in access w/VBA

[orange]

I'm having trouble w/ insert query, I'm using
Insert Into Table [VMSU-ILT]( "Type")
Values("Call")
and I am recieving an error expected end of statement and highlights my perthises, if the compiler doesn't highlight (Type) it highlghth (call)
I am trying to insert Call into the VMSU-ILT table and in field type
I have even tried w/ StrSQL = infront of my statment with it declared as a string and still same error,
note trying to pass value to table in access w/VBA

Trevor,
If your tablename is VMSU-ILT, and your fieldName is Type, and the value you want to add is "Call" then the SQL would be like this:

INSERT INTO [VMSU-ILT](Type) VALUES("Call")

In your example

Insert Into Table [VMSU-ILT]( "Type")
Values("Call")

You do not need the word Table, and you would not have the quotes " around the FiledName Type

Good luck.

[FrymanTCU]

Ok I have been searching the forum trying to find the answer on my own but I have had no luck. I have a form that searches the tbl_UpdateLog and show the filtered records in a sub form. I then want to use the Update function to change the AuditReason field in the underlying table.

One of the problems is which record does this update? If I highlight the line one line of data in the sub form, is that the record I am calling in this function? Also, since I am trying to modify data in the sub form do I need to change the settings to a snapshot or close and re-query the data?

Please help I am fairly inexperienced so sorry if this is a weak question but the help is greatly appreciated.

Thanks,
Rich

Private Sub cmdAccptInError_DblClick(Cancel As Integer)
DoCmd.RunSQL "UPDATE UpdateLog SET AuditReason = ""Accepted in Error"" WHERE " _
& " [UniqueID]= RTS_Audit.RTS_AuditSubform.[UniqueID];"
End Sub

[Trevor]

FryManTCu: you sql would update the [AuditReason] field to [Accepted in error] were the uniqe id eqle the uniqe id on your subform

[FrymanTCU]

Trevor: That's what I was hoping but it is not getting the UniqueID from the subform for some reason. I have been trying the code many different ways, with different "" & and (()()) combination but have had not luck. I did get a message box for the UniqueID and when I entered it in manually the proper record was updated. Please help, I bet this is some simple bone head mistake on my part but it is really frustrating.
Thanks

[Trevor]

[VBA]
DoCmd.RunSQL "UPDATE UpdateLog SET AuditReason = ""Accepted in
Error"" WHERE " _
& " [UniqueID]= '" RTS_Audit.RTS_AuditSubform.[UniqueID]=";"
[\VBA]
Notice the quotes, there are needed in the query so uniqueID = (whatever it equals) not "uniqleID=", which the query knew to look at that field but didn't know what to do

[FrymanTCU]

Private Sub cmdAccptInError_DblClick(Cancel As Integer)
Dim mySQL As String
mySQL = "UPDATE UpdateLog SET AuditReason = ""Accepted in Error"" WHERE " _
& "UniqueID = '" & Me.RTS_AuditSubform.[UniqueID] & "';"
DoCmd.RunSQL mySQL

Gives me a run-time error '438' Object doesn't support this property or method.

I am really getting frustrated with this, I have changed around the code a million different ways and it still won't work. When I don't get an error the table is not updated. Again this is a subform which has a list of records in the datasheet view. I select the record then dbl_click the button which is part of the main form footer. PLEASE ANYONE FIX THIS STUPID CODE

[Trevor]

ok, it looks like by your argument your are running the query from your subform, but if the button that tiggers the query to run is on the main form, then you need to change me.RTS_auditsubform
To
[vba]
Forms![Mainformname]![Subformname]![UnitID] & "' ;"
[\VBA]
I sometimes run into problem with subform controles wantin to run when referd to them by the main form, so if that doesn't work try making a hidden controle on your main form with the value u want to compair and
write code on your subform that onchange or after undate the controle u placed on the main form = the subform control value, and adjust the query line above to compaire against that.
If you need more help you know where to find me :beerchug:

I Think that will fix your error

[FrymanTCU]

THANKS! I was really about to throw my machine out the window, I knew it was something simple. I have never received any coding training, I have been learning through macros and other people's code which is helpful but very difficult! Again, thanks for your help, I'm sure I'll need your help again soon.
-Rich

[Trevor]

keep learning, the best way to learn is to do, not copy :beerchug:
so what did you do that actualy worked?

[FrymanTCU]

I had to use the [Forms]!.... syntax to call the UniqueID from my subform. I new there was something wrong with that variable but I didn't know what. It is working fine and I don't want to mess with it but if anyone know a quick fix for updating multiple records, say you select 2 lines from a subform datasheet, how would you update that? Would I need some kind of loop? As of now if I select two lines it only updates the first record so I assume I need a "do if" or Loop statement but I have plenty of other stuff to mess with so it will have to wait.

Private Sub cmdAccptInError_DblClick(Cancel As Integer)
Dim mySQL As String
mySQL = "UPDATE Log SET AuditReason = ""Accepted in Error"" WHERE " _
& "UniqueID = " & [Forms]![RTS Audit]![RTS AuditSubform]![UniqueID] & ";"
DoCmd.RunSQL mySQL
End Sub

[Trevor]

Glad to see that you got it done, I have an inert query post and missing opererator post, Would you mind taking a look ,I think I just need a fresh pair of eyes to look at, (I've been staring at my code too long)