Hello all,
I have been looking for a solution to this problem...
Here is what I would like to do:
I have a new DB (DB2), with slightly different design. I want to allow the users to look up materials in the old DB (DB1) and add that to a new order.
However, DB2 uses two tables for the materials and a join table for the Order ID and Material ID, and also includes quantity, price, etc. (line item details basically.)
A user opens the order form from the main form. Clicks a button to open the search form, selects (via checkboxes, from DB1) which materials to order, clicks the "Add" button. Search form closes, back to order form. Enter any remaining info necessary for the order, then click "Submit".
What I would like to do is this:
• Search for any existing materials using the description and the part number in DB2
• If a part number exists, use a pop-up form for the user to confirm the correct material, look up the ID and insert into the corresponding field. (This line item details are contained on a sub form/data sheet on the order form. Currently, the sub form is bound to a temp table, which does not need to have an FK.)
• If a matching description is found, do the same thing.
• Then, insert data into the appropriate tables.
If a user manually enters the part numbers and description, then a lookup would need to happen as well, as described above.
If there are no matches to anything in DB2, then a new record will be created in the appropriate tables.
The issue I have run into is this: If I have one record selected on the search form, add that to the sub form (line item details), and retrieve a matching ID, all is good. But if I have more than one, the retrieved IDs overwrite the ID on the first record. I have not been able to successfully move to the next record in the sub form and insert another ID.
This code in on the order form:
I have tried each of the above lines to reference the sub form/datasheet and move to its next record, and get errors each time. The first few lines returned the 'field could not be found', others an 'invalid method in expression', and 'form is not open'. (I think I deleted the lines that returned 'field not found'. The three Goto Record commands all say the form is not open)
Hope this makes sense.
Mike
I have been looking for a solution to this problem...
Here is what I would like to do:
I have a new DB (DB2), with slightly different design. I want to allow the users to look up materials in the old DB (DB1) and add that to a new order.
However, DB2 uses two tables for the materials and a join table for the Order ID and Material ID, and also includes quantity, price, etc. (line item details basically.)
A user opens the order form from the main form. Clicks a button to open the search form, selects (via checkboxes, from DB1) which materials to order, clicks the "Add" button. Search form closes, back to order form. Enter any remaining info necessary for the order, then click "Submit".
What I would like to do is this:
• Search for any existing materials using the description and the part number in DB2
• If a part number exists, use a pop-up form for the user to confirm the correct material, look up the ID and insert into the corresponding field. (This line item details are contained on a sub form/data sheet on the order form. Currently, the sub form is bound to a temp table, which does not need to have an FK.)
• If a matching description is found, do the same thing.
• Then, insert data into the appropriate tables.
If a user manually enters the part numbers and description, then a lookup would need to happen as well, as described above.
If there are no matches to anything in DB2, then a new record will be created in the appropriate tables.
The issue I have run into is this: If I have one record selected on the search form, add that to the sub form (line item details), and retrieve a matching ID, all is good. But if I have more than one, the retrieved IDs overwrite the ID on the first record. I have not been able to successfully move to the next record in the sub form and insert another ID.
This code in on the order form:
Code:
Set db = CurrentDb
Set rs = db.OpenRecordset("INV_Items", dbOpenSnapshot)
Set rs2 = db.OpenRecordset("INV_Item_Supplier", dbOpenSnapshot)
Set rs3 = Me.DS.Form.RecordsetClone
With rs3
.MoveLast
.MoveFirst
Do Until .EOF
sDesc = !Description
sPN = !PartNumber
rs.FindFirst "[Description] LIKE ""*" & sDesc & "*"""
If Not rs.NoMatch Then
Debug.Print "ItemID: " & rs!ItemID
' DoCmd.OpenForm "RFP_F_L2_ItemFoundConfirmContinue", acNormal, , "[ItemID] = " & rs!ItemID, acFormReadOnly, acDialog, 1
vItem = getValueFromPopUp("RFP_F_L2_ItemFoundConfirmContinue", "txtItemID", rs!ItemID, 1)
Me.DS.Form!ItemID = CInt(vItem)
' DoCmd.GoToRecord acDataForm, "RFP_DS_L3_LineItems_Temp", acNext ' form is not open
' DoCmd.GoToRecord acDataForm, "Forms!RFP_F_L1_Request!DS.Form", acNext ' form is not open
' DoCmd.GoToRecord acDataForm, "Forms!RFP_F_L1_Request!RFP_DS_L3_LineItems_Temp.Form", acNext ' form is not open
' Forms!RFP_F_L1_Request!DS.Form.SetFocus 'invalid method
' Me.DS.Form.SetFocus 'invalid method
Me!DS.Form!txtItemID.SetFocus
End If
I have tried each of the above lines to reference the sub form/datasheet and move to its next record, and get errors each time. The first few lines returned the 'field could not be found', others an 'invalid method in expression', and 'form is not open'. (I think I deleted the lines that returned 'field not found'. The three Goto Record commands all say the form is not open)
Hope this makes sense.
Mike