Double Click subform to add to table after user enty

[chimp8471]

Evening

I am trying to streamline my database, it is a stock management database that allows users to book out stock.. It works perfectly but to be honest the process sis a bit long winded..

I am therefore looking to ease things.

I have a form with a subform.. I want to be able to double click a record, a form would then pop up with some of the info from that record prompting the user to enter the qty they want to remove and this data then passed to a table.

I have attached an image of what i am trying to do.. but to be honest i have no idea where to start.

Any help or guidance would be really appreciated.

Thanks in advance

Andy

[pbaldy]

This may help with the first part.

http://www.baldyweb.com/wherecondition.htm

One way to accomplish the second:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName WHERE 1=0"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  rs.AddNew
  rs!PartNumber = Me.PartNumber
  rs!Qty = Me.Qty
  rs.Update

  rs.Close
  set rs = nothing
  set db = nothing

your picture raises the fact that you're displaying a text part number but saving a numeric ID.

[pbaldy]

Edited to make the items needing your actual names red.

[chimp8471]

Hi Sorry for the delay in replying... been in surgery having a knee replacement....

Huge thanks for the feedback....

I am not very good at this so if you could help me break this down a little please:

1.DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName

For my second form... do i need to create a table that sits behind the 2nd form? or is that controlled from the original data?

2. Can i set the double click for any field on the row? or does it have to be a specific field?

I have added a stripped down version of what i have as i can confuse myself sometimes...(to be clear i have done nothing from the feedback as i wanted to be clear first on my questions above)

doubleclick.zip

Thanks in advance

[pbaldy]

The second form would be based on the table itself or a query that returned all records.

The double click could be behind any field, it just has to refer to the key field.

[chimp8471]

ok so i have the 2nd form and created a double click on the part number and it magically loaded the 3 fields (absolutely no idea how that happened) totally guessing with this lol

Next Question....

How do i now add a user input field to the form where they can enter the number? as this is not in my original table or form....

this is what i have so far...

[chimp8471]

So i added a text box that allows me to enter a number... added a button!

Private Sub AddToCart_Click() Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset


  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName WHERE 1=0"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


  rs.AddNew
  rs!PartNumber = Me.PartNumber
  rs!QTY = Me.QTY
  rs.Update


  rs.Close
  Set rs = Nothing
  Set db = Nothing
End Sub

and put code on click of the button but it confuses me at this bit...

strSQL = "SELECT * FROM TableName WHERE 1=0"

What table am i Select * from???? or is this my new form (Frm_CartAdd_Item) or the original Table that led me to the double click (tPartdescription)

How does it know to go to My other table (tTempPartsIssued)

rs.AddNew
rs!PartNumber = Me.PartNumber
rs!Qty = Me.Qty
rs.Update


this bit... for me to transfer the PARTID Not the (Part Number) and New entered Item textbox (RemoveQty) is this close?


rs.AddNew
rs!PartID = Me.PartID
rs!RemoveQty= Me.RemoveQty
rs.Update

[pbaldy]

TableName is the table you want to insert this record into, tTempPartsIssued. What you have look like it should work.

[chimp8471]

ok so you are a genius!! Thanks so much for your help

got lots of other bits i want to do with this but will raise fresh posts as they may help others.

I do have one final question on this section.....

When i add to the new table, i have a list box on my form to show whats been added to the cart..... however thats not refreshing...although the items are adding to the table

I have tried putting this on the button code but it's not refreshing....

Option Compare Database




Private Sub AddToCart_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb()

strSQL = "SELECT * FROM tTempPartsIssued WHERE 1=0"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)


rs.AddNew
rs!PartNumber = Me.PartID
rs!QTY = Me.RemoveQty
rs.Update


rs.Close
Set rs = Nothing
Set db = Nothing
Me.ListBoxcart.Requery

End Sub

[pbaldy]

That should work, presuming the listbox is on the popup form. If it's on the original form:

Forms!FormName.ListBoxcart.Requery