Still stuck! - Getting data from a form field to input data to table

[blyxx86]

I'm having a difficult time here, and am not sure what needs to be done to get this working.

I have a textbox on a form who's control source is

=IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" And [cboKeypad]="Pass" And [cboRadio]="Pass" And [cboStructural]="Pass"),"Pass","Fail")
 

I want the value of this field after it computes to be placed into the field "Overall" on my database.

So far this code has not worked, it input a new record into the table, but did not update the current record I was actually creating with my form:

Dim strSQL As String
strSQL = "INSERT INTO QC(Overall) Values([cboOverall])"
DoCmd.RunSQL (strSQL)
End Sub
 

This code did not do anything at all ([ID] is bound to the field ID on my table]

Private Sub cboOverall_AfterUpdate()
Dim strSQL As String
strSQL = "UPDATE QC SET Overall='" & [cboOverall] & "' WHERE ID =" & [ID] & ";"
DoCmd.RunSQL (strSQL)
End Sub
 

Any idea on how to do this?

[MMcCarthy]

I'm having a difficult time here, and am not sure what needs to be done to get this working.

I have a textbox on a form who's control source is

=IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" And [cboKeypad]="Pass" And [cboRadio]="Pass" And [cboStructural]="Pass"),"Pass","Fail")
 

I want the value of this field after it computes to be placed into the field "Overall" on my database.

Remove the statement from the control source.

Set the Control Source to the field Overall.

You could put the statement in the default value but I don' think that will work. You will have to put the statement in code similar to the below in the before update event of the control [Overall].

 
Private Sub Overall_BeforeUpdate()
 
   Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
      " And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
      " And [cboStructural]="Pass"),"Pass","Fail")
 
End Sub
 

[blyxx86]

Remove the statement from the control source.

Set the Control Source to the field Overall.

You could put the statement in the default value but I don' think that will work. You will have to put the statement in code similar to the below in the before update event of the control [Overall].

 
Private Sub Overall_BeforeUpdate()
 
   Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
      " And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
      " And [cboStructural]="Pass"),"Pass","Fail")
 
End Sub
 

Bah! I thought about that yesterday and thought it wouldn't work.
I have tried with the default value, but it didn't work, you were correct.
I will do this. Thank you.

[MMcCarthy]

Bah! I thought about that yesterday and thought it wouldn't work.
I have tried with the default value, but it didn't work, you were correct.
I will do this. Thank you.

You're welcome.

Let me know if it works ok.

Mary

[blyxx86]

You're welcome.

Let me know if it works ok.

Mary

Still no.

I am messing with the code now, i'm not sure if it's just not formatted properly or not. I am retrying a few things and rewriting it a bit.

I'll let you know in a minute.

[MMcCarthy]

Still no.

I am messing with the code now, i'm not sure if it's just not formatted properly or not. I am retrying a few things and rewriting it a bit.

I'll let you know in a minute.

Try putting the code in the after update event of the last combo box to be changed.

[blyxx86]

Try putting the code in the after update event of the last combo box to be changed.

Perhaps I can put it on the button I press at the end of the form? In the OnClick function?

The code is coming up with an error..

"Syntax error."

The entire thing is red and is all selected when I try to compile the code.

I still don't understand how VBA is meant to be formatted and whether or not things use quotes, parenthesis, single quotes, brackets, ampersands, etc...

Ok.. it comes up with a more specific error when I forgot to change the name of the control to meet my actual control.

expected: )

On the second line, it selects Pass and gives the error. No idea.

[MMcCarthy]

Sorry that's my fault. Try this and put it in the click event, sounds like a good idea.

 
Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
       And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
       And [cboStructural]="Pass"),"Pass","Fail")
 

[blyxx86]

Sorry that's my fault. Try this and put it in the click event, sounds like a good idea.

 
Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
       And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
       And [cboStructural]="Pass"),"Pass","Fail")
 

I modified it to just be on one line and it seemed to work, I only kinda understand how it pieces all of the " and ' and () and []... Eventually..

I'm hoping tonight I'll be able to sit down and go through and do some basic reading on how they are pieced together, because that is the only thing really confusing me. How it is all put together so that VBA can actually use what I type as valid.

I have a question.. When do I determine whether to type something like...

Me.Overall.Text/Value or to just leave it as Me.Overall ? I know that I can change the properties of the field using Me.Overall.DefaultValue/etc, but what is "text" and "value" ?

[MMcCarthy]

IIf(<Expression>, <Value if true>, <Value if False>) is a function.

The second set of brackets
i.e. ([cboScanner]="Pass" And [cboDisplay]="Pass" And [cboKeypad]="Pass" And [cboRadio]="Pass" And [cboStructural]="Pass")
is used to tidy up the expression.

[cboKeypad] square brackets are used around any field or table names. They can also be used around any other object name like controls, forms, queries, etc.

"Pass" is the value if the expression is true, quotation marks indicate a string value
"Fail" is the value if the expression is false, quotation marks indicate a string value

I used the & _ to tell the code that the statement wasn't finished but continued on the next line.

Mary

[blyxx86]

IIf(<Expression>, <Value if true>, <Value if False>) is a function.

The second set of brackets
i.e. ([cboScanner]="Pass" And [cboDisplay]="Pass" And [cboKeypad]="Pass" And [cboRadio]="Pass" And [cboStructural]="Pass")
is used to tidy up the expression.

[cboKeypad] square brackets are used around any field or table names. They can also be used around any other object name like controls, forms, queries, etc.

"Pass" is the value if the expression is true, quotation marks indicate a string value
"Fail" is the value if the expression is false, quotation marks indicate a string value

I used the & _ to tell the code that the statement wasn't finished but continued on the next line.

Mary

The brackets are used only inside of a function though, correct?

String values, do they output as text? I know I had to do quite a bit of tweaking to get a DefaultValue to be changed via VBA, because the default value needs quotes within quotes and VBA wasn't liking that.

So you have to create something that is...
Const cQuote = """"

Yet """" returns only a single " to the function. How does it return only one? I see two sets. Or is it just a mystery of code. The other one is lost to heat or something (physics joke).

[MMcCarthy]

The brackets are used only inside of a function though, correct?

The round brackets, yes.

String values, do they output as text? I know I had to do quite a bit of tweaking to get a DefaultValue to be changed via VBA, because the default value needs quotes within quotes and VBA wasn't liking that.

For quotes within quotes you always use single quotes within double quotes. You don't need the const.

[NeoPa]

The brackets are used only inside of a function though, correct?

String values, do they output as text? I know I had to do quite a bit of tweaking to get a DefaultValue to be changed via VBA, because the default value needs quotes within quotes and VBA wasn't liking that.

So you have to create something that is...
Const cQuote = """"

Yet """" returns only a single " to the function. How does it return only one? I see two sets. Or is it just a mystery of code. The other one is lost to heat or something (physics joke).

Quotes are always confusing.
Quotes normally toggle between 'Treat followng data as a literal string' and 'Stop treating following chars as a literal string'.
But consider a string "Please enter dbl-qoutes (") here". When it hits the second ", within (), it will interpret that as the end of the string literal which is not what we want.
There is a standard way around that, which is to double-up quotes contained in a string. Thus the string would actually be referred to in code as "Please enter dbl-qoutes ("") here".
A string, then, which is just a single " character, would be written in code as """".

strSQL = """"
Debug.Print "Result = "; strSQL
Result = "

[blyxx86]

Quotes are always confusing.
Quotes normally toggle between 'Treat followng data as a literal string' and 'Stop treating following chars as a literal string'.
But consider a string "Please enter dbl-qoutes (") here". When it hits the second ", within (), it will interpret that as the end of the string literal which is not what we want.
There is a standard way around that, which is to double-up quotes contained in a string. Thus the string would actually be referred to in code as "Please enter dbl-qoutes ("") here".
A string, then, which is just a single " character, would be written in code as """".

strSQL = """"
Debug.Print "Result = "; strSQL
Result = "

Could you explain that last little piece of code to me? Now there is a semicolon involved and it didn't show up in the output. It's like learning grammar for a new language, that is very disimilar to my current language (English) and also, where would you place that code to see the results you entered?

I now know I could do a msgbox command to show the "strsql" but would still like to be able to see it within VBA. You aksed me in the other thread if I knew about breaks and watches and such. I do not really have any idea. I did put a watch in on one expression, but I don't think it did anything.

[NeoPa]

Semi-colon ( ; ) in a Print steam denotes that the following item should follow on without any break.

Debug.Print """"
Produces the simple result :
"

"Result = " resolves to
Result =
then """" resolves to
"
Could have been written as

strSQL = """"
Debug.Print "Result = " & strSQL
Result = "