Command Buttons

[sha123]

I've got a table called tblingredients I now need to make a form that stores the information of the table(tblingredients) in the tblorders table,I need to use command buttons!

So say I've got Butternut in the tblingredients table,I then will need a button in the new form that when I click on it the pc knows the value is butternut and it must add it to the tblorders folder!
How will I do this and what will the code be!

 

[mincefish]

This could be quite long winded, so I'm going to write the code here, but it might be easier if I could send you a sample database!!

As an overall idea, I'd have a unique list of ingredients from tblIngredients displayed in a combo box. When a user selects one of those ingredients from the combo box, and clicks on a command button, it will add the ingredients to tblOrders. Simple as that eh? There are still a couple of things I'm unsure about though.
1) Does each ingredient added to tblOrders have to be associated to an order Id? If so, how do we know what that order is?
2) Do we need any other information to be stored in tblOrders?

For the moment, I'm going to assume that the answer to question 1 is that there is a text box on the form that holds the order id, and that needs to be added to tblOrders.
And to answer question 2 also, I'm assuming this is the layout of tblOrders:

OrderID* IngredientID* DateAdded
------- ------------ ----------
0001 0001 12/12/2002
0001 0002 12/12/2002
0001 0004 13/12/2002
0002 0002 13/12/2002

*Primary Keys that stop duplicate ingredients being added for the same order.

tblIngredients, I'm assuming looks like this:

IngredientID IngredientDescr
------------ ---------------
0001 Flour
0002 Butter
0003 Squirrels
0004 Butternut


On our new form, we have:

1* text box called

txtOrderID

holding the
order id - how this gets there is outside the scope of this
currently
1* combo box called

cboIngredients

holding a
list of all the ingredients in tblIngredients
1* command button called

cmdAddIngredient

.
which we will click on to add the ingredient to tblOrders

Firstly, we need to set the Column Count in the combo box
to 2 - we need to columns, as we want to hold the ID (in a
hidden column that no-one will see, but we will use to
write into tblOrders), and then the description (that the
user will see). Then set the Column widths to

0cm;5cm

- this makes the first column, the bound column,
invisible.

Under Row Source, add this query:

SELECT DISTINCT tblIngredients.IngredientsID, 
tblIngredients.IngredientsDescr
FROM tblIngredients;

This will make our combo box show all the ingredients in
tblIngredients.

Now for the code that will add the ingredients when the
user clicks on the button:

Private Sub cmdAddIngredients_Click()
On Error GoTo Err_Func
Dim rstAdd As DAO.Recordset
Dim strSQL As String

If Me.cboIngredients.Value = vbNullString Then
    MsgBox "You have not selected an ingredient to add to 
the order list!"
    Exit Sub
End If

strSQL = "SELECT * FROM tblOrders;"

Set rstAdd = CurrentDb.OpenRecordset(strSQL, , dbAppendOnly)

rstAdd.AddNew
rstAdd.Fields("OrderID") = Me.txtOrderID.Value
rstAdd.Fields("IngredientID") = Me.cboIngredients.Value
rstAdd.Update

Err_Exit:
    Exit Sub

Err_Func:
    If Err.Number = 3022 Then
        ' Ingredient has already been added (we are 
violating the primary key restrictions)
        MsgBox "The ingredient [" & Me.cboIngredients.Column
(1) & "] has already been added to order [" & 
Me.txtOrderID.Value & "]"
        Exit Sub
    End If
    MsgBox "There was an error adding your ingredient " & 
vbNewLine & vbNewLine & "The ingredient [" & 
Me.cboIngredients.Column(1) & "] has already been added to 
order [" & Me.txtOrderID.Value & "]"
    Exit Sub

End Sub

The above code will check to make sure something has been
chosen from the combo box by the user. It then opens a DAO
recordset (if using access2k, then a reference to DAO needs
to be set), and adds the details.

The error handling looks to check the error wasn't one
concerning primary key violation (in which case the
ingredient has already been added to the order list for
that orderid), otherwise it simply warns that the record
wasn't added.

I hope I've covered everything here. I'm sure you will have
some questions about all of this - I may be offline for a
few weeks, but I'll try to get back to you asap. If you
want to give me an e-mail address, I'll zip this database
up, and send it to you - I've been building it as I go
along, and it might be easier for you to learn from.

Tom