Edit an auto populated field

[Cheri L.]

How can an edit an auto populated field? After selecting the item description from a drop down combo box, the item code and resin wieght fields are auto poplulated. However, if the item descriptioin is not yet available the user can type in a description of a "new" product. Then this is done the next 2 fields do not auto populated which is what I expect but I want to be able to add the resin weight of the product. I can not do that because the field will not let me type in it. Any help or direction would be VERY much appreciated

 

[Mod]

Can you explain how the other two fields are autoupdated?

Is it because changing the combo box changes the current record of the form?

Or is it because the combo box has an AfterUpdate event that changes the next two controls?

 

[Cheri L.]

Product1(Item description) is a combo box. The control source of the next 2 fields which are textboxes is as follows
Item Number =Product1.Column(1)
Resin Weight =Product1.Column(2)

The fields get autofilled with the correct information.

By the way this is my first thread and I have not been working with Access long. I am definitely learning as I go.

Thanks for any help you can give.

 

[Mod]

I think I see. I would probably adjust the design a little bit. You have a table of products, right? it might look something like this:

tblProducts
ItemNumber (Autonumber) (primary key)
ItemDescription (Text)
ItemWeight (Double)

To make the form with 3 controls behave close to the way you like, here is the approach I would take:

• Set the form's Record Source property to "SELECT * FROM tblProducts;"
• Select the control source of each text box from the options that drop down.
• Set the combo box's row source property to "SELECT ItemNumber, ItemDescription FROM tblProducts;"
• Set the combo box's Bound Column, Column Count, and Column Widths properties to "1", "2", and "0;1" respectively
• Set the combo box's name to cboChooser
• Add this code to the AfterUpdate event of the combo box:
  

  Dim rs As Object
  Set rs = Me.Recordset.Clone
  rs.FindFirst "[ItemNumber] = " & Str(Nz(Me![CboChooser], 0))
  If Not rs.EOF Then Me.Bookmark = rs.Bookmark

• Make a new command button, set its caption to "New Product" and its name to "cmdNew" and its click event to this:
  

  DoCmd.GoToRecord , , acNewRec

There are a few problems with the design i just listed above. First there is no method to enter new descriptions, a new text box would be required. Second the Item Number (Auto Number) won't be editable by the user. You should never really use the autonumber type if the data is supposed to be meaningful to the user.

 

[Mod]

I wasn't explaining that very well, so I made a quick example of what I was trying to do