Record value from 3rd open form to table bound to 1st form (1 Viewer)

[Zydeceltico]


Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "


In the above example - what is happening in the WHERE clause? Trying to understand this to use it.

It appears to me that SQL = "Update TutorAvailability SET IsAvailable = False " is setting a field named TutorAvailbilty to False given CRITERIA after the WHERE clause or are the fields "Name" and "UniClassName" being update in the WHERE clause? If that is the case then what is happening in the SET statement SQL = "Update TutorAvailability SET IsAvailable = False " ?

Thanks for your patience,

Tim

 

[jdraw]

Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "

SQL is the variable of type string.
IsAvailable is a boolean column in table TutorAvailability that we want to set to False
WHERE
Me.ListAvail.Column(0) is string that equals the Name column in table TutorAvailability AND
Me.ListAvail.Column(2)is string that equals the UniClassname column in table TutorAvailability AND
column isAvalable is True

Me refers to the current form and ListAvail is a listbox on the form.

Booleans do not require any enclosing quotes.
Numbers do not require any enclosing quotes
String values do require any enclosing quotes
Dates require enclosing #

 

[Zydeceltico]

Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "

SQL is the variable of type string.
IsAvailable is a boolean column in table TutorAvailability that we want to set to False
WHERE
Me.ListAvail.Column(0) is string that equals the Name column in table TutorAvailability AND
Me.ListAvail.Column(2)is string that equals the UniClassname column in table TutorAvailability AND
column isAvalable is True

Me refers to the current form and ListAvail is a listbox on the form.

Booleans do not require any enclosing quotes.
Numbers do not require any enclosing quotes
String values do require any enclosing quotes
Dates require enclosing #

So what would the syntax look like if I wanted to SET a value to a second field? In other words:
SET IsAvailable = False
SET MyFavoriteColor = Green
........before getting to the WHERE clause?

Remembering that I am going to use the approach you describe of putting the SQL in a variable.

And - critical - the values are going to come from comboboxes - not just a value that preexists. I found how to do that. I can't seem to find any info on the syntax, comma placement, etc. of how to have multiple fields updated in the SET clause - especally when the values are user-provided via combo boxes or other controls. It's easy to find info on updating a single field from a single control but I can't find what I need for multiple cbos and everything I've tried is throwing an error.

 

[jdraw]

Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& ", MyFavoriteColor ='Green' " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "

Generic pattern for Update query:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The tricky part is creating the SQL from within vba. You have to respect the variable types and the enclosing quotes etc.

Here's a video from Phil S (codekabinett) that puts several things together.
And info from Isladogs' site on SQL/VBA

 

[Zydeceltico]

Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& ", MyFavoriteColor ='Green' " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "

Generic pattern for Update query:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

The tricky part is creating the SQL from within vba. You have to respect the variable types and the enclosing quotes etc.

Here's a video from Phil S (codekabinett) that puts several things together.
And info from Isladogs' site on SQL/VBA

Thank you for this - especially the links to the tutorial and IslaDog's page. Just finished them both. I learned a ton from Phil's video. Suddenly feel much more confident with the comma placement, ampersands, and such!

Now I just need to try it.
This afternoon.

 

[Gasman]

PMFJI,

I tend to Debug.Print the SQL string to see if I have it correct.?

 

[Zydeceltico]

PMFJI,

I tend to Debug.Print the SQL string to see if I have it correct.?

I caught that tip as well. Thank you. Makes a lot of sense.

 

[Zydeceltico]

Here's the code I put together:

    Dim strSQL As String
    
    strSQL = "UPDATE tblCampingTrip " & _
        " SET tblCampingTrip.MiscThoughts = Me.[txtMiscThoughts], " & _
        " tblCampingTrip.TravelMode = Me.[cboTravelMode] " & _
        " WHERE tblCampingTrip.CampingTrip_ID = Me.[tempCampingTrip_ID] ;"
        
        CurrentDb.Execute strSQL, dbFailOnError
        
        Debug.Print strSQL
        
        DoCmd.Close

When I run it from frmGetMiscThoughts it gives me error 3061 - Too few parameters. Expected 3.

The VBA window ends on and highlights the line: "CurrentDb.Execute strSQL, dbFailOnError"

I don't know what that means. Thoughts?

I've attached the mockup db.

Thanks.

 

[Gasman]

You MUST concatenate your variables with the strings ?

The Debug.Print would show you your error?

Also you are meant to debug.print BEFORE the Execute?
Even comment out the execute until you get the syntax correct.?

 

[Zydeceltico]

You MUST concatenate your variables with the strings ?

This part I do not understand. The Immediate window just shows me the SQL as I intended it. Which led me to believe I had it correctly.

Can you give me an example of concatenating "your variables with the strings?"

Does this line: " SET tblCampingTrip.MiscThoughts = Me.[txtMiscThoughts], " & _
need to look like this: " SET tblCampingTrip.MiscThoughts = " & Me.[txtMiscThoughts] & ", " & _

Does the comma at the end need another set of quotation marks?

The Debug.Print would show you your error?

Also you are meant to debug.print BEFORE the Execute?
Even comment out the execute until you get the syntax correct.?

Got it. Thanks.

 

[Gasman]

No I would expect to see something like

SET tblCampingTrip.MiscThoughts = 3

where Me.[txtMiscThoughts] is equal 3

SQL has no knowldedge of form controls

Look at the first thread on this page?, that has the correct syntax as it is concatenating the control values to build a string that SQL will understand.?

HTH

 

[Zydeceltico]

No I would expect to see something like

SET tblCampingTrip.MiscThoughts = 3

where Me.[txtMiscThoughts] is equal 3

SQL has no knowldedge of form controls

Look at the first thread on this page?, that has the correct syntax as it is concatenating the control values to build a string that SQL will understand.?

HTH

I am certain that this: "SET tblCampingTrip.MiscThoughts = 3" is not what the final code will look like. This update query is used to take user input from an unbound form with unbound controls and place them in a table filling in fields of a partially completed record.

The Immediate window shows nothing unless I run the VBA from the form as the query needs user input into a textbox and a cbo before it can be fired. When I run it in the VBA window I get asked for a macro because the code is looking for values that don't yet exist.

Here's what I have for code now:

Private Sub cmdSaveAndExit_Click()
    Dim strSQL As String
   
    strSQL = "UPDATE tblCampingTrip " & _
        " SET MiscThoughts = '" & Me.txtMiscThoughts & "' " & _
        " TravelMode = '" & Me.cboTravelMode.Column(0) & " ' " & _
        " WHERE CampingTrip_ID = '" & Me.[tempCampingTrip_ID] & "' ;"
       
        Debug.Print strSQL
       
        CurrentDb.Execute strSQL, dbFailOnError

It compiles without issue.

Given that I entered the word "patience" in Me.txtMiscThoughts and selected "trains" from the combo box Me.cboTravelMode.Column(0), this is what I see in the Immediate window:

UPDATE tblCampingTrip  SET MiscThoughts = 'patience'  TravelMode = 'trains '  WHERE CampingTrip_ID = '2' ;

And I get error 3075, Syntax error (missing operator) in query expression "patience' TravelMode = 'trains"

I'm guessing I have too many or not enough apostrophes/quotation marks.

Any idea which ones?

Thanks,

Tim

 

[Gasman]

Well you are almost there?

https://docs.microsoft.com/en-us/of...ference/update-statement-microsoft-access-sql

You need a comma between the fields being set.?
You do not use single quotes for numbers unless they have been defined as text.

Reread jdraw's post in post #22 for what each type of variable requires when concatenating.

 

[Zydeceltico]

Well you are almost there?

https://docs.microsoft.com/en-us/of...ference/update-statement-microsoft-access-sql

You need a comma between the fields being set.?
You do not use single quotes for numbers unless they have been defined as text.

Reread jdraw's post in post #22 for what each type of variable requires when concatenating.

I reread jdraw's #22. I've tried multiple variations. The two variables in the SET clause are both strings. The variable in the WHERE clause (CampingTrip_ID) is a number. It is an autonumber in tblCamping trip. The number is passed from an originally and still open form via OpenArgs to the frm that this code is attached too. I have an unbound textbox on this final form where I can see that the value of CampingTrip_ID was actually passed.

Clearly it is because it shows up in the Immediate window correctly.

Rght now I have this code:

    Dim strSQL As String
 
    strSQL = "UPDATE tblCampingTrip " & _
        " SET MiscThoughts = '" & Me.txtMiscThoughts & "' , " & _
        " TravelMode = '" & Me.cboTravelMode & "' " & _
        " WHERE CampingTrip_ID = '" & Me.tempCampingTrip_ID & "' ;"
     
        Debug.Print strSQL
     
        CurrentDb.Execute strSQL, dbFailOnError

Running that gives me this in the Immediate window:

UPDATE tblCampingTrip  SET MiscThoughts = 'moon' ,  TravelMode = 'automobiles'  WHERE CampingTrip_ID = '5' ;

And I get error message: 3464, Data type mismatch in criteria expression.
The code fails on and highlights "CurrentDB.Execute strSQL , dbFailOnError

So I think that " WHERE CampingTrip_ID = '" & Me.tempCampingTrip_ID & "' ;" must be the culprit because of post #22 it looks to me like I shouldn't have quotes around & Me.tempCampingTrip_ID & so I remove them so the code looks like this:

    Dim strSQL As String
 
    strSQL = "UPDATE tblCampingTrip " & _
        " SET MiscThoughts = '" & Me.txtMiscThoughts & "' , " & _
        " TravelMode = '" & Me.cboTravelMode & "' " & _
        " WHERE CampingTrip_ID = & Me.tempCampingTrip_ID & ;"
     
        Debug.Print strSQL
     
        CurrentDb.Execute strSQL, dbFailOnError

which gives me this in the Immediate window:

UPDATE tblCampingTrip  SET MiscThoughts = 'moon' ,  TravelMode = 'automobiles'  WHERE CampingTrip_ID = & Me.tempCampingTrip_ID & ;

and throw error 3075, SYntax error (missing operator0 in query expression 'CampingTrip_ID = Me.tempCampingTrip_ID &'
The code fails on and highlights "CurrentDB.Execute strSQL , dbFailOnError again.

Since the original code at least gave me the correct values in the Immediate window, I try adding some commas back to this code:

Private Sub cmdSaveAndExit_Click()
    Dim strSQL As String
 
    strSQL = "UPDATE tblCampingTrip " & _
        " SET MiscThoughts = '" & Me.txtMiscThoughts & "' , " & _
        " TravelMode = '" & Me.cboTravelMode & "' " & _
        " WHERE CampingTrip_ID =" & Me.tempCampingTrip_ID & " ;"
     
        Debug.Print strSQL
     
        CurrentDb.Execute strSQL, dbFailOnError

I get this in the Immediate window:

UPDATE tblCampingTrip  SET MiscThoughts = 'moon' ,  TravelMode = 'automobiles'  WHERE CampingTrip_ID = 5 ;

...........and finally it works!!!!!!!!!!!

It wrote the "moon" and "automobile" values to tblCampingTrip and everything. GEEEEEEEEEZZZZZZZZZZZZZZZ

Thanks

Eureka......now if I only understood what is happening with the quotes around Me.tempCampingTrip_ID - but my brain is fried.

 

[Gasman]

Ok, we finally got there.

I could have written it for you, but you would learn next to nothing from that.

Look at this (your first almost correct) attempt.

UPDATE tblCampingTrip  SET MiscThoughts = 'moon' ,  TravelMode = 'automobiles'  WHERE CampingTrip_ID = '5' ;

You put single quotes around the number (which is actually a number, not a number as a string), so by the rules jdraw mentioned you do not use the single quotes, that is for text strings? If that field had been text and you still used numbers in it, then you would use those single quotes.
That is why Access is saying type mismatch as you are trying to compare a number field CampingTrip_ID with a texxt string '5' and it will not do it.

This is basic VBA that you will use time and time again, so you need to understand the syntax.

Another tip. Google for the syntax. There is a lot of info out there and will save you a lot of time, rather than guessing with permutations.

Good luck with the rest of it.

 

[Zydeceltico]

Ok, we finally got there.

I could have written it for you, but you would learn next to nothing from that.

Look at this (your first almost correct) attempt.

UPDATE tblCampingTrip  SET MiscThoughts = 'moon' ,  TravelMode = 'automobiles'  WHERE CampingTrip_ID = '5' ;

You put single quotes around the number (which is actually a number, not a number as a string), so by the rules jdraw mentioned you do not use the single quotes, that is for text strings? If that field had been text and you still used numbers in it, then you would use those single quotes.
That is why Access is saying type mismatch as you are trying to campare a number field CampingTrip_ID with a texxt string '5' and it will not do it.

This is basic VBA that you will use time and time again, so you need to understand the syntax.

Good luck with the rest of it.

One more question - because I am beginning to see that the quotes aren't actually around the Me.tempCampingTrip_ID but rather around " WHERE CampingTrip_ID = " - so I get that part.

But just so I understand: the ampersands are ONLY concatentating correct? They have no bearing whatsoever on data type - correct?

 

[Gasman]

Correct.

 

[Zydeceltico]

Correct.

And if that is the case then why are there double quote around Me.txtMiscThoughts outside of the ampersands in this line:

" SET MiscThoughts = '" & Me.txtMiscThoughts & "' , " & _

instead of reading like this:

" SET MiscThoughts = " &  " Me.txtMiscThoughts " & " , " & _

I'm just curious how the the syntax for the string variables works.

Thanks.

 

[Gasman]

Write it like you have and then debug.print that.?

That will not have the single quote around your text variable, so access will complain.
You need the correct delimiters for each type and it just happens that numbers do not have any.

All you can do is practice and refer back to this code any time you are unsure.

 

[jdraw]

Good stuff. I've been busy with some yard work, and I see Gasman has given great advice. Now you're cookin'.

Re your last post:

And if that is the case then why are there double quote around Me.txtMiscThoughts outside of the ampersands in this line:

Code:
" SET MiscThoughts = '" & Me.txtMiscThoughts & "' , " & _

instead of reading like this:
Code:
" SET MiscThoughts = " & " Me.txtMiscThoughts " & " , " & _

I'm just curious how the the syntax for the string variables works.

This is a string " SET MiscThoughts = '" . When it is processed, the single quote will be the left end delimiter of the value of & Me.txtMiscThoughts which is a text datatype. The & is simply a concatenation symbol. During the rendering process where "Access" deciphers the vba, the value in
Me.txtMiscThoughts gets concatenated to the SET MiscThoughts = '. The & "' in &"' , " forms the right delimiter(the closing quote) of the string. The comma in that &"' , " separates the assignments in the SET statement.

The second example does NOT put the quotes around the string value in Me.txtMiscThoughts.
I said it was tricky, and the debug.print lets you check for errors before ever executing the SQL.