Goto Next Record on Sub Form, from Main form (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 02:03
Joined
Jul 9, 2015
Messages
424
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:
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:03
Joined
Feb 19, 2002
Messages
42,981
I'm pretty sure you are making this harder than it needs to be. Why are you not just importing the data from the old database?
 

mjdemaris

Working on it...
Local time
Today, 02:03
Joined
Jul 9, 2015
Messages
424
Good question, Pat, I wish I could. The new DB has a slightly different table design and an import would not work.

1638918757924.png


This is the system in use, going to be the old DB. The RequestDetails table currently stores the Part Number and Description.

1638918895265.png


This is going to be the new DB. I have separated out the Part Number and Description, as I think they should be (?), to work with Inventory management as well. From the Inventory side, the Items table is the basic Item, Item-Supplier provides the many to many relationship, e.g. we could purchase basically the same thing from multiple suppliers. Working off that principle, the Item-Request table joins the Users Request for Purchase with an Item.

Maybe I've made this too complicated? I'm trying to make this as 'Normal' as I can, i.e. goal is 3rd NF and that's the design I came up with.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:03
Joined
Feb 19, 2002
Messages
42,981
You can use queries to convert the old data to the new format. There is no need to do a straight import of table to table. The conversion is a one time event (or actually, multiple times until you get it right:). The final conversion at cut over is one time)

Doing what you are trying to do means that the old database never dies and someone has to keep updating it.
 

Users who are viewing this thread

Top Bottom