Pass value to combo box

[esee]

I have an unbound text box (txtSaveVal), an unbound combo box
(cboName), and a string variable strSaveVal.

me.txtSaveVal=strSaveVal .............. Works fine
me.cboName=strSaveVal ............... Does Not Work

I simply want to put the value of strSaveVal into the combo box, as
though I had typed it in, BEFORE pressing the enter key.

Why isn't this working?

 

[Al Campagna]

esee,
Why are you doing this?
A combo box is not intended to accept a value from another control,
but provide a means to select a value from a list of values either from a
table,
a query, or a manually entered value list.

though I had typed it in,

Then why not just type it into the combo in the first place?

Please explain in more detail why you're using a text control to
place a value in a combo box.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[esee]

esee,
    Why are you doing this?
    A combo box is not intended to accept a value from another control,
but provide a means to select a value from a list of values either from a
table,
a query, or a manually entered value list.


    Then why not just type it into the combo in the first place?

    Please explain in more detail why you're using a text control to
place a value in a combo box.
--
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

Thanks for responding, Al.

When entering a value in cboName, and executing the "Not in List"
procedure, I pass the value that was entered to a Text Box control on
another Form2.
I then add addition information as necessary on Form2 to update the
underlying table without having to enter the value again from the
cboName on Form1.

This works well.

But this only creates a new value in the table. I now want to go back
to Form1 and, without retyping the value again, use the value that was
already entered and just press the enter key to select the new value.
I can't get this last part to work. When I requery or refresh Form1,
then I lose the original value entered.

Do you have a way for me to do this?

 

[Al Campagna]

esee,
If you could Top-Post during this thread with me, that would help us
keep the sequence of the problem in order. Thanks...

On my website (below), I have a sample 97 and 2003 file called
Not In List Combobox.
It demonstrates how to add a new value to a combobox, after the
Not In List has fired.
It updates the underlying table, and the new value is
automatically added to the combo's list... without any need to reenter the
original value.
You'll just need to add the opening of Form2 within my code sequence.
When you return to Form1 the combo should be all updated and valued.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

esee,
Why are you doing this?
A combo box is not intended to accept a value from another control,
but provide a means to select a value from a list of values either from a
table,
a query, or a manually entered value list.


Then why not just type it into the combo in the first place?

Please explain in more detail why you're using a text control to
place a value in a combo box.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks for responding, Al.

When entering a value in cboName, and executing the "Not in List"
procedure, I pass the value that was entered to a Text Box control on
another Form2.
I then add addition information as necessary on Form2 to update the
underlying table without having to enter the value again from the
cboName on Form1.

This works well.

But this only creates a new value in the table. I now want to go back
to Form1 and, without retyping the value again, use the value that was
already entered and just press the enter key to select the new value.
I can't get this last part to work. When I requery or refresh Form1,
then I lose the original value entered.

Do you have a way for me to do this?

 

[esee]

esee,
    If you could Top-Post during this thread with me, that would helpus
keep the sequence of the problem in order.  Thanks...

    On my website (below), I have a sample 97 and 2003 file called
Not In List Combobox.
    It demonstrates how to add a new value to a combobox, after the
Not In List has fired.
    It updates the underlying table, and the new value is
automatically added to the combo's list... without any need to reenter the
original value.
    You'll just need to add the opening of Form2 within my code sequence.
When you return to Form1 the combo should be all updated and valued.
--
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
   http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."








Thanks for responding, Al.

When entering a value in cboName, and executing the "Not in List"
procedure, I pass the value that was entered to a Text Box control on
another Form2.
I then add addition information as necessary on Form2 to update the
underlying table without having to enter the value again from the
cboName on Form1.

This works well.

But this only creates a new value in the table.  I now want to go back
to Form1 and, without retyping the value again, use the value that was
already entered and just press the enter key to select the new value.
I can't get this last part to work.  When I requery or refresh Form1,
then I lose the original value entered.

Do you have a way for me to do this?

I don't know what you mean by Top Post. I'm using Google Groups and
it seems to be setup to enter my reply at the bottom of the list.

You are not understanding my problem.

FORM1 SQL
SELECT zIngredients.IngredientID, zIngredients.NeedIT,
zIngredients.Ingredient, zIngredients.LocationID
FROM zIngredients
ORDER BY zIngredients.Ingredient;

Private Sub cboName_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into qIngredients ([Ingredient]) values
('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When "Not In List" is executed, I get a run-time error 3201, you
cannot add or change a record because a related record is required in
zLocations.

This is why I'm using FORM2, to update zLocations with Ingredient and
a Location. I then want to go back to FORM1
to select an Ingredient.

 

[Al Campagna]

esee,
I can't test your code, but it appears to be OK...

Sounds like you have a relationship associated with zLocations and
your combo's RowSource table, and that is not allowing the code to
update the combo's RowSource table without zLocations involved.
And this is correct, because you shouldn't be updating just the Many
side of
a One to Many realtionship.
Probably, by using Form2, your properly associating that combo table
with
zLocation, and thereby allowing your RowSource table to updated with
zLocations
blessing...

Refresh Form2 just before closing and Requery the combo on Form1, and...
if necessary, update the combo with the value you just added in Form2 .
You call that cboName? I hope you don't have a field named [Name]...
that's a reserved word in Access and should be avoided.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

esee,
If you could Top-Post during this thread with me, that would help us
keep the sequence of the problem in order. Thanks...

On my website (below), I have a sample 97 and 2003 file called
Not In List Combobox.
It demonstrates how to add a new value to a combobox, after the
Not In List has fired.
It updates the underlying table, and the new value is
automatically added to the combo's list... without any need to reenter the
original value.
You'll just need to add the opening of Form2 within my code sequence.
When you return to Form1 the combo should be all updated and valued.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."








Thanks for responding, Al.

When entering a value in cboName, and executing the "Not in List"
procedure, I pass the value that was entered to a Text Box control on
another Form2.
I then add addition information as necessary on Form2 to update the
underlying table without having to enter the value again from the
cboName on Form1.

This works well.

But this only creates a new value in the table. I now want to go back
to Form1 and, without retyping the value again, use the value that was
already entered and just press the enter key to select the new value.
I can't get this last part to work. When I requery or refresh Form1,
then I lose the original value entered.

Do you have a way for me to do this?

I don't know what you mean by Top Post. I'm using Google Groups and
it seems to be setup to enter my reply at the bottom of the list.

You are not understanding my problem.

FORM1 SQL
SELECT zIngredients.IngredientID, zIngredients.NeedIT,
zIngredients.Ingredient, zIngredients.LocationID
FROM zIngredients
ORDER BY zIngredients.Ingredient;

Private Sub cboName_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into qIngredients ([Ingredient]) values
('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When "Not In List" is executed, I get a run-time error 3201, you
cannot add or change a record because a related record is required in
zLocations.

This is why I'm using FORM2, to update zLocations with Ingredient and
a Location. I then want to go back to FORM1
to select an Ingredient.

 

[esee]

Is Form2 a subform on Form1, or what exactly?  It may be easier to follow if
you describe the forms.

Is error message 3201 being generated by the Not In List event?  I assume so,
but you didn't specify.  A further assumption is that it happens on the
Execute line of code.

When I have used Not In List to update a table it has been either a lookup
table or a top level table in a relationship hierarchy, so related records do
not come into play.  It sounds as if you need to use the Not In List event to
generate a child record, so first you need to create a parent record.

If that is the case, I think you will need to open a recordset to add the
parent record before using the existing code to update the combo box list..

esee,
    If you could Top-Post during this thread with me, that would help us

[quoted text clipped - 73 lines]

Do you have a way for me to do this?

I don't know what you mean by Top Post.  I'm using Google Groups and
it seems to be setup to enter my reply at the bottom of the list.

You are not understanding my problem.

FORM1 SQL
SELECT zIngredients.IngredientID, zIngredients.NeedIT,
zIngredients.Ingredient, zIngredients.LocationID
FROM zIngredients
ORDER BY zIngredients.Ingredient;

Private Sub cboName_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
   If UserResponse = vbYes Then
       sqlAddState = "Insert Into qIngredients ([Ingredient])values
('" & NewData & "')"
       CurrentDb.Execute sqlAddState, dbFailOnError
       Response = acDataErrAdded
   Else
       Response = acDataErrContinue
   End If
End Sub

When "Not In List" is executed, I get a run-time error 3201, you
cannot add or change a record because a related record is required in
zLocations.

This is why I'm using FORM2, to update zLocations with Ingredient and
a Location.  I then want to go back to FORM1
to select an Ingredient.

Okay, I'll try my best to provide you with the necessary information
to help me. Keep in mind, that this is working fine except for one
minor little thing that I want to happen. I'll show you code and then
make comments as to what happens, and what I want to happen.

FORM1 Record Source SQL
SELECT zIngredients.IngredientID, zIngredients.NeedIT,
zIngredients.Ingredient, zIngredients.LocationID
FROM zIngredients
ORDER BY zIngredients.Ingredient;

cboName is an unbound combo box with the following Row Source
SELECT qIngredients.IngredientID, qIngredients.Ingredient
FROM qIngredients
ORDER BY qIngredients.Ingredient;

cboName has a " On Not In List" event as follows:
Private Sub cboName_NotInList(NewData As String, Response As Integer)
Dim byt As Byte, stDocName As String
If OldNewData = NewData Then
OldNewData = ""
End
Else
OldNewData = NewData
End If
Me.Text23 = NewData
Me.cboName.Undo
byt = MsgBox("Do you want to add a new Ingredient to the Lookup
Table?", vbYesNo)
If byt = vbYes Then
Response = acDataErrContinue
stDocName = "frmAddIngredients2"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
End If
End Sub

Text23 saves the value entered in cboName for use on FORM2

FORM2 Record Source SQL
SELECT zIngredients.IngredientID, zIngredients.NeedIT,
zIngredients.Ingredient, zIngredients.LocationID
FROM zIngredients
ORDER BY zIngredients.Ingredient;

The On Load Event of FORM2
Private Sub Form_Load()
If CurrentProject.AllForms("ShoppingList").IsLoaded Then
strWhichForm = "ShoppingList"
Me.Ingredient.Value = Forms![ShoppingList]![Text23]
End If
End Sub

This fills the text box "Ingredient" with the saved value that fired
the "On Not In List" event on FORM1
I then specify the Location that this Ingredient belongs to, and exit
FORM2. I now have a new ingredient that can be used on FORM1

When I exit FORM2, FORM1 fires the Activate Event, which does a
Refresh, and Requery. The new ingredient is now available to be used
by FORM1.

THE MINOR PROBLEM
I do not want to manually enter the New Ingredient again. I want to
somehow fill the cboName with the New Ingredient, and then press enter
to move on to the next field. Minor problems are worth fixing too...

Thanks for any help you may provide.