Hi Adam,
Here's some air-code, I'd put the this in the after update of whatever
control you are grabbing the associated data from, say the field is txtPFS
that the user is filling out, and the one you want to autofill is
txtAcctCode, it'd be something like this:
Private Sub txtPFS_AfterUpdate()
Dim strPFS as String
strPFS = Me.txtPFS
Select Case strPFS
Case 1
Me.txtAcctCode = "account code 1"
Case 2
Me.txtAcctCode = "account code 2"
Case 3
Me.txtAcctCode = "account code 3"
EndSelect
End Sub
Or,if you're pulling the actual fill for txtAcctCode from another form it's
a bit trickier, but can be done, reference the form like this:
Forms!OtherFormName.ControlName
So you end up with something like this:
Private Sub txtPFS_AfterUpdate()
Dim strPFS as String
strPFS = Me.txtPFS
Select Case strPFS
Case 1
Me.txtAcctCode = Forms!OtherFormName.ControlName1
Case 2
Me.txtAcctCode = Forms!OtherFormName.ControlName2
Case 3
Me.txtAcctCode = Forms!OtherFormName.ControlName3
EndSelect
End Sub
Below are some links and more info on Select Case, best of luck!
;-)
SusanV
Sure, here's a couple of links:
http://www.blueclaw-db.com/select_case.htm
http://www.functionx.com/vbaccess/Lesson06b.htm
Visual Basic Help is (I think) clearer:
Select Case Statement
Executes one of several groups of statements, depending on the value of an
expression.
Syntax
Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else
[elsestatements]]
End Select
The Select Case statement syntax has these parts:
Part Description
testexpression Required. Any numeric expression or string expression.
expressionlist-n Required if a Case appears. Delimited list of one or
more of the following forms: expression, expression To expression, Is
comparisonoperator expression. The To keyword specifies a range of values.
If you use the To keyword, the smaller value must appear before To. Use the
Is keyword with comparison operators (except Is and Like) to specify a range
of values. If not supplied, the Is keyword is automatically inserted.
statements-n Optional. One or more statements executed if
testexpression matches any part of expressionlist-n.
elsestatements Optional. One or more statements executed if
testexpression doesn't match any of the Case clause.
Remarks
If testexpression matches any Case expressionlist expression, the statements
following that Case clause are executed up to the next Case clause, or, for
the last clause, up to End Select. Control then passes to the statement
following End Select. If testexpression matches an expressionlist expression
in more than one Case clause, only the statements following the first match
are executed.
The Case Else clause is used to indicate the elsestatements to be executed
if no match is found between the testexpression and an expressionlist in any
of the other Case selections. Although not required, it is a good idea to
have a Case Else statement in your Select Case block to handle unforeseen
testexpression values. If no Case expressionlist matches testexpression and
there is no Case Else statement, execution continues at the statement
following End Select.
You can use multiple expressions or ranges in each Case clause. For example,
the following line is valid:
Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber
Note The Is comparison operator is not the same as the Is keyword used in
the Select Case statement.
You also can specify ranges and multiple expressions for character strings.
In the following example, Case matches strings that are exactly equal to
everything, strings that fall between nuts and soup in alphabetic order, and
the current value of TestItem:
Case "everything", "nuts" To "soup", TestItem
Select Case statements can be nested. Each nested Select Case statement must
have a matching End Select statement.
Select Case Statement Example
This example uses the Select Case statement to evaluate the value of a
variable. The second Case clause contains the value of the variable being
evaluated, and therefore only the statement associated with it is executed.
Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5, inclusive.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case 9 To 10 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select
F1stman said:
Hello Susan,
Thank you for the quick response. Is there anywhere you know that I can
learn more about the case statement. I'm not a whiz when it comes to VBA.
There isn't much info in the assistance section of the Microsoft website
or
in this discussion group. Thanks, Adam
SusanV said:
Hi F1stman,
Personally I would use a Case statement, as it will be easier to extend
in
the future should you need to add PFS options:
Select Case PFS
Case 1
'what you want to happen
Case 2
'what you want for PFS of 2
'etc etc
End Select
--
hth,
SusanV
F1stman said:
Hello All,
I am trying to create an if statement to select the correct account
code
on
one form and place that code onto the current form. Something like:
If PFS=1 then set value to [Central Exhibit].[Account Code 1] or if If
PFS=2
then set value to [Central Exhibit].[Account Code 2] or If PFS=3 then
set
value to [Central Exhibit].[Account Code 3], etc.
PFS is on the current form and Account code is obviously on another
form
which is open at the same time. There are six total possibilities. I
can
get
it to work with one IIf statement. After that I'm sunk. Thank you in
advance.
This group is sooo helpful and had taught me much. Adam
