Adding values to Combo box values

[Mercy]

Hello,

I have a combo box that pulls it's values from table A,
with fields areaOfBody & areaCode
The combo box pulls the values from column: "areaCode"

I want to be able to add values to the combo box. The
tricky part is I need to add "areaOfbody" & "areaCode" at
the same time.

Below is my code:

Dim insertSql As String
Dim intMsgDialog As Integer

intMsgDialog = vbYesNo + vbQuestion

Beep
If MsgBox("Add to List?" , intMsgDialog, "Entry Not
in List") = vbYes Then

insertSql = "INSERT INTO Placement( AreaOfBody,
Code)" & _
" VALUES ('" & NewData & "' , X)"
Debug.Print insertSql
CurrentDb.Execute insertSql
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

 

[Mercy]

Sorry! Clicked "Send" before I finished asking! -_-

Ok ... so to explain the code and it's problems really
quick. The code I posted will put in the NewValue into
the "areaOfBody" field and "X" into the "areaCode"
field. But I don't want "areaCode" to always be "X".

This Insert Sql runs fine as an append Query ... the X
can be made into a parameter. But in VB I have to put
single quotes around the X fo the sql to run.

Maybe I could just open Table "Placement" when the user
clicks "Yes" ... and let the user enter the entries like
that?

If anyone has any ideas! I'd love to hear them!

thanks,
Mercy

 

[Dirk Goldgar]

Hello,

I have a combo box that pulls it's values from table A,
with fields areaOfBody & areaCode
The combo box pulls the values from column: "areaCode"

I want to be able to add values to the combo box. The
tricky part is I need to add "areaOfbody" & "areaCode" at
the same time.

Below is my code:

Dim insertSql As String
Dim intMsgDialog As Integer

intMsgDialog = vbYesNo + vbQuestion

Beep
If MsgBox("Add to List?" , intMsgDialog, "Entry Not
in List") = vbYes Then

insertSql = "INSERT INTO Placement( AreaOfBody,
Code)" & _
" VALUES ('" & NewData & "' , X)"
Debug.Print insertSql
CurrentDb.Execute insertSql
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

One simple possibility is to call InputBox, instead of MsgBox, to let
the user both confirm the addition and get the desired code. It might
look like this:

Dim insertSql As String
Dim strNewCode As String

Beep
strNewCode = InputBox( _
"'" & NewData & "'" is a new value. " & _
"If you want to add it to the list, please type " & _
"a new code for it and click OK or press Enter. " & _
"If you don't want to add it, click Cancel or " & _
"press Esc.", _
"Add to List?"

If Len(strNewCode) > 0 Then

insertSql = _
"INSERT INTO Placement( AreaOfBody, Code)" & _
" VALUES ('" & NewData & "', '" & strNewCode & "')"
CurrentDb.Execute insertSql
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Note: the above assume that Code is a text field, and that it won't
contain a single-quote character.

 

[Mercy]

Hey Dirk,

Thanks for tellng me about the spam thing. And replying
to my post about combo boxes. But ... your reply to teh
combo box was "unavailable" (-_-")

My combo box pulls it's values from Table A. Can I open
Table A on "not in list event" ... and omsehow update teh
combo box like that?

Thanks,
Mercy

 

[Dirk Goldgar]

Hey Dirk,

Thanks for tellng me about the spam thing. And replying
to my post about combo boxes. But ... your reply to teh
combo box was "unavailable" (-_-")

Odd. I'll post it again here:

------------- quoting my previous reply -------------
One simple possibility is to call InputBox, instead of MsgBox, to let
the user both confirm the addition and get the desired code. It might
look like this:

Dim insertSql As String
Dim strNewCode As String

Beep
strNewCode = InputBox( _
"'" & NewData & "'" is a new value. " & _
"If you want to add it to the list, please type " & _
"a new code for it and click OK or press Enter. " & _
"If you don't want to add it, click Cancel or " & _
"press Esc.", _
"Add to List?"

If Len(strNewCode) > 0 Then

insertSql = _
"INSERT INTO Placement( AreaOfBody, Code)" & _
" VALUES ('" & NewData & "', '" & strNewCode & "')"
CurrentDb.Execute insertSql
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

Note: the above assume that Code is a text field, and that it won't
contain a single-quote character.