combo box lookup won't allow new record

[David_Beginner via AccessMonster.com]

Main form has a combo box with client list. Upon selecting client the
subform loads a datasheet with the client's orders. this is accomplished
using the common script:

Private Sub combo5_afterupdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clients]![ClientID] = " & Str(Nz(Me![Combo5], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This works great except that after including this code I am not able to
create a new record.
Or in other words a new order id will not be created in the orders table.

When I take the code out and make Orders.OrderID the control source I am able
to add a new record, the autonumber orderid is automatically created.
However, the combobox can no longer pull up the associated order after update.

And the records have to be navigat throught the nav buttons.

I am assuming that something in the code needs to be altered or added, but
have not been able to figure out.

Below is the basic setup:
TblClients
TblOrders
TblOrderDetails
TblProducts

Main Form:
Query Clients.ClientID(PK AN), Orders.OrderID(PK AN)

ComboBox Unbound
After_update from code above

Sub Form:
Query OrderDetails.OrderID, ProductID / Products.Product/ID

Linked on Orders.OrderID and OrderDetails.OrderID

Thanks,

david

 

[Rick Brandt]

Main form has a combo box with client list. Upon selecting client the
subform loads a datasheet with the client's orders. this is
accomplished using the common script:

Private Sub combo5_afterupdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[clients]![ClientID] = " & Str(Nz(Me![Combo5], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This works great except that after including this code I am not able
to create a new record.
Or in other words a new order id will not be created in the orders
table.

When I take the code out and make Orders.OrderID the control source I
am able to add a new record, the autonumber orderid is automatically
created. However, the combobox can no longer pull up the associated
order after update.

And the records have to be navigat throught the nav buttons.

I am assuming that something in the code needs to be altered or
added, but have not been able to figure out.

Below is the basic setup:
TblClients
TblOrders
TblOrderDetails
TblProducts

Main Form:
Query Clients.ClientID(PK AN), Orders.OrderID(PK AN)

ComboBox Unbound
After_update from code above

Sub Form:
Query OrderDetails.OrderID, ProductID / Products.Product/ID

Linked on Orders.OrderID and OrderDetails.OrderID

As you have seen, a ComboBox (or any other control really) can be used as a
navigation device OR be bound to a field in the form's RecordSource. You
cannot use the same control to do both things at the same time. Use a
separate control as the bound control for data entry and use the one with
code ONLY as a navigation tool.

 

[David_Beginner via AccessMonster.com]

Rick, Thank you very much.

Main form has a combo box with client list. Upon selecting client the
subform loads a datasheet with the client's orders. this is

[quoted text clipped - 39 lines]

Linked on Orders.OrderID and OrderDetails.OrderID

As you have seen, a ComboBox (or any other control really) can be used as a
navigation device OR be bound to a field in the form's RecordSource. You
cannot use the same control to do both things at the same time. Use a
separate control as the bound control for data entry and use the one with
code ONLY as a navigation tool.