By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

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

blyxx86
100+
P: 256
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
Expand|Select|Wrap|Line Numbers
  1. =IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" And [cboKeypad]="Pass" And [cboRadio]="Pass" And [cboStructural]="Pass"),"Pass","Fail")
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO QC(Overall) Values([cboOverall])"
  3. DoCmd.RunSQL (strSQL)
  4. End Sub
  5.  

This code did not do anything at all ([ID] is bound to the field ID on my table]
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboOverall_AfterUpdate()
  2. Dim strSQL As String
  3. strSQL = "UPDATE QC SET Overall='" & [cboOverall] & "' WHERE ID =" & [ID] & ";"
  4. DoCmd.RunSQL (strSQL)
  5. End Sub
  6.  
Any idea on how to do this?
Nov 29 '06 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Expand|Select|Wrap|Line Numbers
  1. =IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" And [cboKeypad]="Pass" And [cboRadio]="Pass" And [cboStructural]="Pass"),"Pass","Fail")
  2.  
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].

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Overall_BeforeUpdate()
  3.  
  4.    Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
  5.       " And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
  6.       " And [cboStructural]="Pass"),"Pass","Fail")
  7.  
  8. End Sub
  9.  
Nov 30 '06 #2

blyxx86
100+
P: 256
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].

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Overall_BeforeUpdate()
  3.  
  4.    Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
  5.       " And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
  6.       " And [cboStructural]="Pass"),"Pass","Fail")
  7.  
  8. End Sub
  9.  
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.
Nov 30 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 30 '06 #4

blyxx86
100+
P: 256
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.
Nov 30 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 30 '06 #6

blyxx86
100+
P: 256
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.
Nov 30 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry that's my fault. Try this and put it in the click event, sounds like a good idea.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
  3.        And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
  4.        And [cboStructural]="Pass"),"Pass","Fail")
  5.  
Nov 30 '06 #8

blyxx86
100+
P: 256
Sorry that's my fault. Try this and put it in the click event, sounds like a good idea.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Me.Overall = IIf(([cboScanner]="Pass" And [cboDisplay]="Pass" & _
  3.        And [cboKeypad]="Pass" And [cboRadio]="Pass" & _
  4.        And [cboStructural]="Pass"),"Pass","Fail")
  5.  
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" ?
Nov 30 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 30 '06 #10

blyxx86
100+
P: 256
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).
Nov 30 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 30 '06 #12

NeoPa
Expert Mod 15k+
P: 31,660
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 """".
Expand|Select|Wrap|Line Numbers
  1. strSQL = """"
  2. Debug.Print "Result = "; strSQL
  3. Result = "
Dec 1 '06 #13

blyxx86
100+
P: 256
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 """".
Expand|Select|Wrap|Line Numbers
  1. strSQL = """"
  2. Debug.Print "Result = "; strSQL
  3. 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.
Dec 1 '06 #14

NeoPa
Expert Mod 15k+
P: 31,660
Semi-colon ( ; ) in a Print steam denotes that the following item should follow on without any break.
Expand|Select|Wrap|Line Numbers
  1. Debug.Print """"
  2. Produces the simple result :
  3. "
"Result = " resolves to
Result =
then """" resolves to
"
Could have been written as
Expand|Select|Wrap|Line Numbers
  1. strSQL = """"
  2. Debug.Print "Result = " & strSQL
  3. Result = "
Dec 1 '06 #15

Post your reply

Sign in to post your reply or Sign up for a free account.