Add record, then show it on the form

[BruceM]

I have a standard Purchase Order database, with a main PurchaseOrder table
(tblPO) and a related line items table (tblPO_Items). These are represented
to the user as a form/subform.
There is a Products table (tblPO_Product), listing products according to
Supplier, Description, Code, Unit, and Unit Price. Unit Price may be Null.
There is a combo box on the Line Items subform that gets it Row Source from
tblPO_Products. If the product is not in the drop-down list, the Not In
List event opens a pop-up form on which the user can fill in the
information. When that is done there is a button to run the following code.
The code inserts a new record into tblPO_Product, then grabs the key field
from the new record and inserts a record into tblPO_Items (the Line Items
subform table).
I don't know if this is the best way to go about this, but it works in terms
of inserting the new record into tblPO_Products, and inserting a new record
into tblPO_Items. The missing piece is that the new record cannot be seen
right away on the Line Items subform. If I navigate away from the PO
record, then back to it, the item is there. However, I have so far failed
in all of my attempts to refresh the subform, save the record, or by some
means accomplish what occurs when I navigate away from, then back to the
record.
It is likely I won't see reponses to this thread until Monday, so if I do
not acknowledge replies until then please do not think me rude.

varUnitPrice = Nz(Me.txtNewUnitPrice, "Null")
strDelim = Chr(34)
strNewDescr = strDelim & Me.txtNewDescr & strDelim
strNewCode = strDelim & Me.txtNewCode & strDelim
strNewUnit = strDelim & Me.txtNewUnit & strDelim
lngSuppID = Forms!frmPO!SupplierID

strSQL_NewProd = "INSERT INTO tblPO_Product (SupplierID, " & _
"ProdCode, ProdDescr, ProdUnit, UnitPrice)" & vbCrLf & _
"SELECT " & lngSuppID & ", " & _
strNewCode & ", " & strNewDescr & ", " & _
strNewUnit & ", " & varUnitPrice

DBEngine(0)(0).Execute strSQL_NewProd, dbFailOnError

lngNewProd = DLookup("ProductID", "tblPO_Product", _
"SupplierID = " & lngSuppID & _
" AND ProdCode = " & strNewCode & _
" AND ProdDescr = " & strNewDescr)
lngReqID = Forms!frmPO!ReqID

strSQL_NewLine = "INSERT INTO tblPO_Items (PO_ReqID, " & _
"ProductID, Quantity, UnitPrice)" & vbCrLf
& _
"SELECT " & lngReqID & ", " & _
lngNewProd & ", 1, " & varUnitPrice

DBEngine(0)(0).Execute strSQL_NewLine, dbFailOnError

 

[Evi]

One possible way:
Instead of running an insert query, have your popup based directly on your
Items table.

In the OnGotFocus event of the combo use

Me.YourComboName.Requery

In the notinlist event

Dim MyForm as STring
Dim strMsg As String

MyForm = "FrmItem"
strMsg = "Add new item to list?"

If MsgBox(strMsg, vbYesNo + vbQuestion, NewData & " Does Not Exist") =
vbYes Then
'if they choose yes it will open up a form
'where you need to input the new name
Me.cboItem.Undo
'undo client's attempt
Me.AnotherField.SetFocus
'move the cursor to another field or control in the form
Response = acDataErrContinue
'cboItem has Me.cboItem.Requery so moving back to it
'from this adjacent field
'puts the new data into the list
DoCmd.OpenForm MyForm, acFormDS
Else
Response = acDataErrContinue
'user can choose something else from the list
'which remains open
End If

When the user reselects the item, it will now be there.


Evi

 

[BruceM]

Thanks for the reply. I started developing a way to do this that involved
entering the new record data directly into the subform, but it looked like
even if I succeeded it would have been confusing for the user, so I went to
a pop-up form for the new record. However, I kept the Insert SQL, for no
good reason. I expect you are correct that a form bound to the Products
(Items) table makes more sense than an Insert query.
However, the main drift of the question is that I would like the new item to
be added as a new line items record rather than being available in the combo
box on the line items subform.
By way of example, suppose that I have a Purchase Order for tools from a
hardware supplier. I create the main record (select a supplier, add a date,
and so forth), then I select "Hammer" in the combo box in the line items
subform. The line item then shows the name of the tool, product code, unit,
and unit price, as it should.
Now I type "Screwdriver" into the combo box for the next line item on the
subform. It is not in the list, so the Not In List event runs, where I am
given an opportunity to add it. A form pops up, I add the product code,
etc., and click OK, which requeries the combo box on the line items subform.
Now through the same code I want to do the equivalent of selecting the new
item from the combo box. In other words, once the user has filled in the
necessary information for "Screwdriver", the line item record should contain
that information, rather than sending the user back to the combo box to
select again.

 

[Evi]

I see what you mean, Bruce.

How about using OpenArgs to pass values back and forth between the forms.

See the second method here:
http://support.microsoft.com/kb/197526

Evi

 

[BruceM]

Thanks again for your reply. I can add the new record to the Products
table, and using the second INSERT code I can add the new product as a new
line item to the purchase order line items table. If I navigate to another
main form record (i.e. another purchase order), then back, the new line item
is in place. What I can't figure out is how to requery (or refresh, or
something) the subform recordset so that the new line item shows up
immediately, without the need to go back and forth.

 

[Evi]

So the combo does display the new data but only if you were to close and
then reopen the form?

I'm presuming that you have tried to requery the combo and to save the
record with
If Me.Dirty=True then
Me.Dirty = False
End If

There's probably a better way to do this but I guess one solution is to
put the Mainform's primary key (Me.Parent.NameOfControl) into a variable,
close the form and then immedately re-open it, using the variable to go back
to the same record

Evi

 

[BruceM]

I would like to perform through code the equivalent of selecting the new
item from the combo box. The user has added the item because it is needed
for a line item on the Line Items (order details) subform. Rather than
having the user select the new item from the combo box, I would like it to
appear as a completed line item on the subform(quantity would need to be
filled in, of course), exactly as if the user had selected the item from the
combo box.
I can in fact add the new line item record (the order detail on the subform)
using the Insert SQL I posted originally. It is this new record that does
not appear until I move away from the main form record, then back to it.
All of my attempts to refresh, requery, reload the recordset, etc. have
failed.

 

[BruceM]

Just thought I'd mention that I seem to have solved the problem. The combo
box Not In List event asks the user if they want to add a new record. If
Yes, a pop-up form is opened, with the acDialog argument. This pauses the
code execution while the pop-up form is open. The user enters information
(ProductCode, UnitPrice, etc.) onto the pop-up form. These values are
converted to variables, which are passed back to the Not In List event when
the pop-up form is closed (i.e. when an Add button on the pop-up form is
clicked).

The Not In List event then opens a recordset (the Products table) and
inserts the NewData value, along with the values passed from the pop-up
form, very much like described on the MVPS web site for using the Not In
List event to add a new record to a form.

The next step is to open another recordset (the LineItems table), to which a
record is added in similar manner to how the record was added to the
Products table.

Finally, the line of code:
Response = acDataErrAdded
updates the record on the Line Items form. This is the part that wasn't
happening in my earlier attempts. My mistake was that I was trying to
update the recordset in the pop-up form's Add button Click event, but the
Not In List event still had things to do, and didn't like the interference.
By passing the values back to the Not In List event the problem was solved.

BTW, I expect this would have worked with Insert SQL such as I originally
described, but I think the Recordset code is easier to work with. I expect
I could also have used a form bound to the Products table to add the new
Product record, but I would have had to pass the SupplierID value to this
form, and I couldn't see any advantage to that approach over opening and
updating a recordset.