combo box selection to write a new record in another table

[CSummers]

I have 1 form, it is bound to a REPAIRS table. This table has the fields Product (primary key) Date, NumberUsed. I have a unbound combo box that looks up a product from a PRODUCTS table. I want to select the product, and save a record in the table the form is bound to, REPAIRS. I also want to have a command button that runs an update query on the products table that decrements a field called INSTOCK for that particular product. I am brand new to access and have been trying to do this for 2 days- now I have myself totally confused. Any help would certainly be appreciated. Thank you in advance.

 

[scriverb]

First of all you need to add a field to the Repairs table. This field should have the identfier for the Product being selected. Otherwise, you will never know what the repair is for.

Please details the fields in the Products table like you did with the Repairs table. What is the identifier field, product name field, etc. After that I can give you some code to perform what you need.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[CSummers]

Thanks for the reply. The Products table has the following fields;
Products - a text field that I store a part number- primary key, no duplicates
Description - a test field that I store the description text
Instock- a number field that I store the number of the product in stock.

Cindi

 

[scriverb]

First of all add a field to your Repairs table and call it Products_Numb, text, (same size as Products in the Products table. No index here though.

Your Combobox has a RowSource value. It is probably the table Products. How do you want the user to make their selection: Product Number or Description? I ask this because we are going to change the RowSource to a query and sort it on one or the other of these fields. That way they can just begin typing in this value and the combobox will search for it automatically. Get back with me about which is the easiest for the User to do this.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Sorry about that, you already have a field Product in the Repairs table. Ignore that dumb statement of mine.

You really should have your ComboBox bound to the Repairs table field Product. Otherwise you will be entering it twice. Also, this will make the SAVE of the record to Repairs very easy as all of the information is on the form. Also, the primary key should be more than just the Product field. Otherwise you can only have one record with a product number. Probably a combination of all three fields: Product, Date, and Number Used.

If you want the User to select by the Description. Use the following SQL as the RowSource.

Select A.Description & "(" & A.Products & ")" as Product_Description, A.Products FROM Products as A ORDER BY A.Description;

If you want the User to select by the Products. Use the following SQL as the RowSource.

Select A.Products & " - " & A.Description  as Product_Description, A.Products FROM Products as A ORDER BY A.Products;

Change the Bound Column to 2, Column Widths: 3";0

Create a Command Button on your form with a caption of SAVE. Put the following VBA code in the OnClick Event Procedure of this command button.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDB
If IsNull Me![cboProducts] then
   db.close
   MsgBox "Product selection must be made before SAVE"
   Me![cboProducts].SetFocus
   Exit Sub
End If
Set rs = db.OpenRecordset("Products", dbOpenDynaset)
rs.FindFirst "[Products] = '" & Me![cboProducts].column(1) & "'"
If Not rs.NoMatch then
   rs.Edit
   rs("InStock") = rs("InStock") - Me![NumberUsed]
   rs.Update
   DoCmd.RunCommand acCmdSaveRecord
else
   MsgBox "No Product Record found"
End If
rs.close
db.close

After trying this post back with the results.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[CSummers]

I am still doing something wrong. This is a relatively small database, and I can zip it up and email it to you if you want to see what I am trying to do.

Thanks.
Cindi

 

[scriverb]

Sure, you can find my email address in my profile.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]