-----Original Message-----
well, i don't see any point to adding a record to a table via a recordset,
then opening the table via a form to finish entering data for the new
record. do it once, presumably via the form makes more sense, and be done
with it.
Record source of combo box: "Orders subform"
a combo box doesn't have a RecordSource property, so i assume you mean the
record source of the form that the combo box is on. "Orders subform" doesn't
look to me like it could be a form's record source; it that the *Name
property* of the form?
i think we're just going in circles here. below is an explanation of a
standard use of a combo box where the two tables have a one-to-many
relationship on a specific field. read thru it, and if you understand the
concepts then you should be able to apply them to your own setup, or
determine that this solution is not suitable for your setup (or you may
determine that your setup needs to be changed). Example follows.
tables first:
tblVegetables
VegID (primary key)
VegName
CID (foreign key from following table)
tblColors
CID (primary key)
ColorName
ColorDensity
ColorBrightness
normally, in a form you want to use a combo box to enter the color for each
vegetable record. and in this situation, you also want the user to be able
to add a new color record to tblColors when necessary - during data entry.
so you build a form bound to tblVegetables, as frmVeggies. you bind a combo
box control to the field CID. the RowSource of the combo box is
SELECT CID, ColorName FROM tblColors ORDER BY ColorName;
you set the bound column to 1, and the column widths to 0"; 1". so the
combobox is bound to the CID, but shows the ColorName field in the droplist
during data entry.
you create a second form bound to tblColors. here's where the user is going
to add a new color record when necessary. if you have to generate a primary
key manually or programmatically (if the primary key of tblColors is *not*
an autonumber) here's where you will do it. if tblColors has more fields
that need to be entered in each record (as in my example table above),
here's where the user (or you, programmatically) will do it.
okay, still with me? next, open a public module - or create a new one, if
you don't already have one in the database. add a public variable, as
Public strColor As String
now, back to the form that has the Colors combo box on it. put the following
code in the combo box NotInList event procedure, as
Private Sub Combo0_NotInList(NewData As String, Response As Integer)
If MsgBox("Do you want to add a new color to the list?",
vbDefaultButton2 + vbYesNo) = vbYes Then
strColor = NewData
DoCmd.OpenForm "frmColors", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me!Combo0 = Null
Me!Combo0.Dropdown
End If
in frmColors' OnLoad event, add the following code, as
Private Sub Form_Load()
Me!ColorName = strColor
End Sub
when the user clicks Yes in the message box, the NewData value will be
assigned to the public variable strColor, then frmColors opens and *the rest
of the NotInList event code is suspended until frmColors is closed*.
when frmColors loads, the strColor value (from the user's combo box data
entry in frmVeggies) will populate the ColorName control in frmColors. the
user enters the rest of the information needed to complete a new Colors
record, and closes frmColors.
at this point, the rest of the NotInList event code fires in frmVeggies,
automatically updating the combo box with the new record from tblColors. now
the user can just move on to the next control in the record frmVeggies.
hth, and good luck!
Tina, I'm grateful for your help.
I have somehow stumbled on the way to add my new data to
the "ProductsSold" table and to the "Orders Subform"
subform - so I have partially solved the "NotInList"
problem. It's not working correctly, however, because
when I open the "frmProductsSold" form to add the other
details for the new record, it will not load the new
item. Here is the troublesome code:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmProductsSold"
stLinkCriteria = "[ProductID]=" & "'" & Me![ProductID]
& "'"
DoCmd.OpenForm stDocName, , , , , acDialog, stLinkCriteria
The value in "Me![ProductID]" is null. I think if I can
pick up the product ID, my procedure will work.
Here are the answers to your questions - I hope they help:
First I open the "ProductsSold" table to add the
new "ProductName" (and it creates a new "ProductID"),
then I open the "frmProductsSold" form to add the other
details for the record. I think I only open the table
once.
Combo box row source: SELECT DISTINCT
ProductsSold.ProductID, ProductsSold.ProductName,
ProductsSold.UsedFor, ProductsSold.PartNo,
ProductsSold.UnitPrice
FROM ProductsSold
ORDER BY ProductsSold.ProductName;
Control source: "ProductID"
Record source of combo box: "Orders subform"
Note that the combo box bound column is column 1, but
it's not visible. The combo box displays column
2, "ProductName".
-----Original Message-----
are you opening the ProductsSold table *twice* to add the record? i'm afraid
i don't get it. maybe a little more info will help.
pls
post the RowSource
of the combo box and its' ControlSource, and also post the RecordSource of
the form that the combo box is on. an explanation of
how
the two tables are
linked would be helpful also.
To HTH:
Several other helpful people have offered suggestions
which have not been successful, nor have the two you
suggested. I am sending my code out again - not
sure
if
you saw it the way posted it in the newsgroup.
Here's
my
response to your advice:
My NotInList event code follows. It has 2 problems: 1)
the dialog form doesn't open with the new data and
2)
it
puts the item on the list but won't let me use it.
I
get
the following error message: "The current field must
match the join key '?' in the table that serves as
the 'one' side of the one-to-many relationship. Enter a
record in the 'one' side table with the desired key
value, and then make the entry with the desired join key
in the 'many-only' table."
Thanks!
MY NOTINLIST CODE:
Private Sub Product_NotInList(NewData As String, Response
As Integer)
'Ask the user whether to add a value to the list
Dim strMessage As String
Dim dbsPOs As Database
Dim rstProductsSold As DAO.Recordset
strMessage = "Are you sure you want to add '" &
NewData & "' to the list of products?'"
If Confirm(strMessage) Then
'Open the ProductsSold table and add the NewData
value.
Set dbsPOs = CurrentDb
Set rstProductsSold = dbsPOs.OpenRecordset
("ProductsSold")
rstProductsSold.AddNew
rstProductsSold!ProductName = NewData
rstProductsSold.Update
Response = acDataErrAdded 'Requery the list.
'The next line allows the new data to populate the
other form.
Me.ProductName = NewData
'Open the ProductsSold form to complete it.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmProductsSold"
stLinkCriteria = "[ProductName]=" & "'" & Me!
[ProductName] & "'"
stLinkCriteria = "[ProductID]=" & "'" & Me!
[ProductID] & "'"
DoCmd.OpenForm stDocName, , , , , acDialog,
stLinkCriteria
Else
Response = acDataErrDisplay 'Display the error.
End If
End Sub
-----Original Message-----
add the following to your procedure, *after* the code
that adds the value to
the underlying table:
Response = acDataErrAdded
if you're opening a 2nd form, in the Not In List event,
to add the new value
to the table, then open the form 1) *not* as a datasheet
and 2) in windows
mode Dialog. this suspends the rest of the code until
the form is closed.
if you can't get it to work, suggest you post your
NotInList event code, so
we can see it.
hth
message
I have a subform with a combo box that selects items
from
a list, and limits the items to the list. I use a "not
in list" event to add an item to the list. When I
return
to my first form, the item is not available to select
from the list. However, when I check my table, the
item
was in fact added to the list.
How can I make my new item available on the list
without
leaving my original form?
.
.
