Get Product Code

[adam]

Hi,

I've designed a Data sheet type form from my products table where I've named the from as "Products". The from contains five columns.

I also have another form where I have implied it in a standard from as a sub form(named as "Order Details Subform"). The columns of the sub form are Product code, Description, Category, Quantity, Unit price & Line Total. The column product Code on the Order Details Subform is a look up column.

What I'm trying to get help is how to make the product code to get copied from the "Products" form to the column "Product Code" on the sub form "Order Details" when a user clicks the product code from the "Products" form.

Any help on this would be kindly appreciated.

[HansV]

I don't think this is a good idea. It's much easier to make the product code control on the subform a combo box from which the user can select a product. The combo box can have five columns, so that the user can see all the information when he or she looks at the dropdown list of the combo box.

[adam]

I do respect your suggestion Hans.

But on the other hand, the products (datasheet) form has an advantage. Suppose if there are thousands of products in the product list where the user does not know their code.

If a customer buys such a product where the user does now the code, he could just simply type the product name in the products form and the row containing the product gets filtered with the product code.

This makes the user easier to add the product code to the sub form when the user clicks the product code.

[HansV]

The code would look like this, with the correct names substituted:

Code: Select all

Private Sub Product_Code_Click()
  Forms![Orders Form]![Order Details Subform]![Product ID] = Me![Product ID]
End Sub

[adam]

Thanks for the code. But I'm getting error message when I click the product code from the products from. I've attached the sample database.

[HansV]

I did mention that you would have to substitute the correct names.

The form that contains the subform is not named "Products Form", and the field that identifies the product is not named "Product ID" in your database.

[adam]

You're right Hans. But where in database have I missed the field that identifies the product which i have failed to name as "Product ID" in my database?

[HansV]

What is the name of the field that identifies the product? You have mentioned it earlier in this thread.

[adam]

I've out that Hans. I've found out that Hans.

Thanks for the help. But the product code gets added to the row where the cursor resides.

Meaning if the cursor is in the first row the product code gets added to the first row. If the user try to enter a second product it also gets added to the same row instead of the row below.

Can this be avoided?

[HansV]

Why do you think I mentioned that it was not a good idea?

[adam]

Due to what I've mentioned in Post 26023.

[HansV]

You could use this code, but it's a rather silly way of working.

Code: Select all

Private Sub Product_Code_Click()
  Forms![Create New Invoice]![Order Details Subform]![Product Code] = Me![Product Code]
  Forms![Create New Invoice].SetFocus
  Forms![Create New Invoice]![Order Details Subform].SetFocus
  RunCommand acCmdRecordsGoToNext
  Me.SetFocus
End Sub

[adam]

The above code works fine.

By the way if a user accidentally adds up an extra data row to the the sub form how could he remove that additional row. The only way I could see is to select that row and "Cut" the row by right clicking mouse.

[HansV]

If you click the record selector (the grey box to the left of the record), you can press the Delete key on the keyboard, or click Delete on the ribbon.

x298.png

[adam]

Thanks for the help Hans.

You’ve pointed out that what I’ve asked at the begging of this thread is a rather silly way of working. I guess you've mentioned this by referring to what you've said in your first reply.

I would be happy if you could point out a disadvantage other than what you've mentioned in your first reply; in doing so?

[HansV]

I think it's overkill to use a separate form and VBA code where a combo box would serve the same purpose with much less effort.

[adam]

You're right Hans. I totally agree with what you've mentioned.

On the other hand the advantage is the user now has two ways to enter the product to the sub form.

Meaning, if a product does not has a code, he could quickly search the product from the products form with a search button and a text box and then insert the product to the sub form with a click.

And if the product has got a code he could simply select the number from the combo box as you've preferred.

Anyway, Thanks for the help and recommendations. I do really appreciate them.

[adam]

In cases where some users might remember the code for some products; instead of searching from the long list of the combo box they might want to type the numbers.

Since the code numbers are formatted as “0000” the corresponding columns won’t get filled up if the user writes 1 for example instead of 0001 in the sub form.

Can an environment be created so that when the user types 1 (for 0001) the appropriate columns get filled with data?

Note: it does work fine if the user types 0001 in the combo box.

[HansV]

If you want to be able to type 1, you must remove the format 0000.