how to insert gender (male/female) option group in sql INSERT INTO statement

[mdavid]

Hi,
I have the following:
#
CurrentDb.Execute _
"INSERT INTO [Members] (" & _
"[Family Name], " & _
"[First Name], " & _
"[Date of birth], " & _
"[Gender]" & _
"[MobilePhone]" & _
"[eMail]" & _
"[Comments]" & _
") VALUES (" & _
"'" & Me.FamilyNameTxt & "', " & _
"'" & Me.FirstNameTxt & "', " & _
"#" & Me.DoBTxt & "#, " & _
"'" & MaleOrFemale & "'" & _
"'" & Me.PhoneTxt & "'" & _
"'" & Me.EmailTxt & "'" & _
"'" & Me.CommentsTxt & "'" & _
")"
#

On my form the Gender Option Group is Frame27
the Option Buttons are Option30 (Female) Option Value: -1
Option32 (Male) Option Value: 0

Don't know how to get the value (MaleOrFemale) from the option group.


Any help appreciated.
Thanks
David

[June7]

You reference the option group frame control name. The frame takes the value of the selected button in the group.

[orange]

Are you saying/asking that you want 3 possible values, such as

Option30 (Female) Option Value: -1
Option32 (Male) Option Value: 0
Option34 (MaleOrFemale) Option Value: 1

June has provided info re the selected value in the Frame.

Good luck.

[mdavid]

Thanks for this, still having problems. I want M or F to appear in Gender so I added the following before the INSERT statement:
#
Dim MorF As String
MorF = "M"
If Me.Frame27 = "-1" Then
MorF = "F"
End If
#

I've corrected formatting and replaced MaleOrFemale in the INSERT statement with MorF.

The Insert works without error

But for Gender I only receive "0"

What am I missing?

Thanks
David

[June7]

Don't put -1 in quote marks.

One liner

MorF = IIf(Me.Frame27 = -1, "F", "M")

Or use a combobox with 2 choices.

[orange]

David,
Instead of "'" & MaleOrFemale & "'" & _

Try this line
IIf(Me.Frame27 = 0, "M", "F") & _

and instead of
CurrentDb.Execute _

try Debug.print _ to see what Access understands.

I think you are missing some commas to separate the field values, but lets see what the Debug.print (debugging) shows.

OOOps: June has already responded.

[June7]

Yep. Definitely missing commas for 3 pairs of fields and inputs.

[mdavid]

Hi, Thanks all - problem was had Gender defined as Yes/No field, changed to Short Text all OK