My co-workers are too lazy to copy and paste ...

[FIZGIG]

I'm trying to modify our order sheet to be able to click on an item and it automatically adds it to an order sheet. Is it possible? Thanks.

[moke123]

Yes it's possible but not enough info to provide a more indepth answer. You may also want to use a double click event to prevent accidental clicks.

[FIZGIG]

Thanks for responding. What extra information would you need? I attached an image for the main form used.. Did you see that?

[alansidman]

You would want to use VBA code similar to the following which creates a new record with the old information. You can modify this concept to meet your specific needs.

Private Sub copyrecordbutton_Click()

On Error GoTo Err_copyrecordbutton_Click

Dim txtOld1 As Variant

Dim txtOld2 As Variant

Dim txtOld3 As Variant

Dim txtOld4 As Variant



txtOld1 = txtcurrent1.Value

txtOld2 = txtcurrent2.Value

txtOld3 = txtcurrent3.Value

txtOld4 = txtcurrent4.Value



RunCommand acCmdRecordsGoToNew



txtnew1.Value = txtOld1

txtnew2.Value = txtOld2

txtnew3.Value = txtOld3

txtnew4.Value = txtOld4





Exit_copyrecordbutton_Click:

    Exit Sub



Err_copyrecordbutton_Click:

    MsgBox Err.Description

    Resume Exit_copyrecordbutton_Click



End Sub

[orange]

What extra information would you need?

To offer focused advice/suggestions it would be helpful to
-show a graphic of your tables and relationships in order to understand the "bigger" picture
-some comment regarding the tables from which these forms get their data
-a simple description in plain English of the business involved (do you have Customers, Suppliers,....?)
-a brief overview of the processes involved in your order and inventory activities
-a statement regarding "cost" which rarely is stable with time

Just some thoughts for consideration.
Think of it this way - if you were going to hire someone to "do" what you have in mind, what info would they need to know/have to complete the task? I'm sure you are intimately familiar with the business, the environment you are working in, the people, the processes, any reports etc, etc, but readers are not.

Good luck with your project.

[moke123]

Thanks for responding. What extra information would you need? I attached an image for the main form used.. Did you see that?

In addition to what Orange has pointed out, I would assume you aren't actually copying from form to form but are writing from table to table. It would certainly be helpful to know table names and fields and what info needs to be written.
I would imagine it to be a simple insert into ... select ... where ... statement with a subform requery.

edit: BTW, those look like scratch ticket names, at least in my state.

[FIZGIG]

Thanks again to those that responded. I have attached more snips of a simplified version of what I'm trying to do, does this help?

[moke123]

Assuming you are entering the quantities in your items to order form, it appears you are only writing item code and orderID to the Items_Ordered table.


on double click event of the item code field you would have something like


Dim StrSql As String
StrSql = "Insert into Items_Ordered([Item Code],[Order ID] Values(" & Me.ItemCode & "," & Parent.[Order ID] & ")"
CurrentDb.Execute StrSql, dbFailOnError


Parent.[Items to Order].Requery 'you'll need to set correct relative path to the subform
You should eliminate the spacing in your naming convention. then you wouldnt need to use [] around your names.

heres a quick example
ex_Products.zip

edit: sorry for sloppy example but had to get to work.

[FIZGIG]

Thanks! I made some changes based on your suggestions and have included the example database. I'm getting closer to what I would like it to do but it's still not happening. Thanks again.

[moke123]

You didnt change the name of the table or subform in the code.

[FIZGIG]

You didnt change the name of the table or subform in the code.

Better? But what else did I miss? It's still not working ...

Private Sub ITEM_CODE_DblClick(Cancel As Integer)
Dim strSql As String
strSql = "Insert into ITEMS_ORDERED(ITEM_CODE,ORDER_ID) values(" & Me.ITEM_CODE & "," & Parent.ORDER_ID & ")"
CurrentDb.Execute strSql, dbFailOnError
Parent.ORDER_SHEET_ITEMS.Requery
End Sub

[moke123]

It's still not working

That doesn't tell me much. I corrected the table name and the subform name and it works for me.
What exactly is not working?

[FIZGIG]

That doesn't tell me much. I corrected the table name and the subform name and it works for me.
What exactly is not working?

I double click on item number and nothing happens.

[ssanfu]

After looking at your dB, I think you should stop and have another look at the tables & relationships.

I changed some field and object names. (all uppercase is SHOUTING)
Play around with the attached dB to see if it is close to what you want/trying to do.

Hover over a record that has underlining in the inventory sub form to see a tool tip.



Good luck with your project...

[moke123]

Heres your example back. You can double click the item number or item description in the products subform and it transfers to the ordered form. I also added a default quantity of 1 since it appeared you wanted that as a default. I also added a recalc in the after update of the quantity.

What else is it you want it to do?

BTW, you should consider using a primary key in all your tables.

EXAMPLE_V2.zip