Case statement in Access

[Sara]

Hello,
I have an Inquiry form that has a option group control called Source that
gives the user the option to check if the inquiry is by Phone, E-mail or fax
(Only one can be selected). On submit, I have an insert query that adds a
new inquiry to the Inquiries Table but the Source field in my table is
populated with 1, 2, or 3 which are the values assigned to the Phone, E-mail
or fax options.
Is there a way to insert the text instead of just numbers?
I though maybe having like a case statement in my insert query would work,
but I don't know how to do it.
I would appreciate your help.

 

[Arvin Meyer [MVP]]

Better to use the numbers and build a table with the three values. If the
Primary Key of both fields is an Integer datatype, you'll use 2 bytes
instead of a minimum of 6 and a maximum of 12. If you really want a Case
statement:

Dim str As String

Select Case Me.OpGrpName

Case 1
str = "Phone"
Case 2
str = "E-mail "
Case 3
str = "fax"

Case Else
End Select

Then use the str variable in your insert code. If you are using a saved
query instead of code, you'll need to save the variable in an unbound
textbox that can be referred to in the query.

 

[Sara]

Arvin,

Thanks for the reply, I agree with you on that is better to have a small
table with the option numbers as primary key, that was my first approach, but
for some reason my manager didn't like that idea and told me to use a Case
statement.

Now, I am fairly new coding, so I am not sure where to include the case
statement you posted.
And yes, I am using an insert saved query in access here is an abreviated
version:
where Forms!DataEntry!Frame refers to the group option control.

INSERT INTO Inquiries ( Source, Unit, Terminal, TrainNo )
SELECT Forms!DataEntry!Frame AS Expr1, Forms!DataEntry!Unit AS Expr2,
Forms!DataEntry!Terminal AS Expr3, Forms!DataEntry!Train AS Expr4;

What modification would I need to do?

Thanks so much!

 

[Jeff Boyce]

Pardon my interjection...

If your manager thinks a Case statement (i.e., a "hard-coded" approach)
would work better than a table (easily updated), perhaps s/he is
volunteering to take care of the maintenance that hard-coding will
require...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

"What modification would I need to do?"

1. Take over your manager's job. You are obviously more adept than he is.

2. Find a better job. (See number 1)

3. Add a control named something like txtSource as an unbound control. Then
your code would look like:

Private Sub Frame_AfterUpdate()

Dim str As String

Select Case Me.Frame
Case 1
str = "Phone"
Case 2
str = "E-mail "
Case 3
str = "fax"
Case Else
End Select

Me.txtSource = str

End Sun

and your query would look like:

INSERT INTO Inquiries ( Source, Unit, Terminal, TrainNo )
SELECT Forms!DataEntry!txtSource AS Expr1, Forms!DataEntry!Unit AS Expr2,
Forms!DataEntry!Terminal AS Expr3, Forms!DataEntry!Train AS Expr4;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com