Multiple forms and recordsets, transferring data

[mjdemaris]

Hey all, I have a question similar to this thread:
https://access-programmers.co.uk/forums/showthread.php?t=279709

My answer to "why?" is this: the Request form has a subform which holds the line item details for the Request. It has a field for the RequestID (an FK). The problem arises when a user wants to add an item to the subform from a third form - a search form. The user searches for previously requested items, checks a box for items to add to the current subform.

The Request form and subform were bound, but trying to add items from the search form gave me problems with getting the FK to update. So, I thought unbound forms would work.

I see in the above thread that Rick got an answer, but I don't quite understand CJ's answer.

Thanks.

 

[MarkK]

Maybe you could say what it is about CJ's answer you don't understand. There are a number of interesting things going on there. Code from that thread is...

 dim db as dao.database
 dim rst as dao.recordset
  
 set db=currentdb
 db.execute("INSERT INTO mTable......")
 set rst=db.openrecordset("SELECT @@Identity")
 newID=rst.fields(0)
 set rst=nothing
 set db=nothing

I would likely write it...

   With CurrentDb
      .Execute "INSERT INTO tTable....", dbFailOnError
      With .OpenRecordset("SELECT @@Identity")
         newID = .Field(0)
         .Close
      End With
   End WIth

...but you could add a record using a recordset too and also get the newID...

   With CurrentDb.OpenRecordset("tTable")
      .AddNew
      !Field1 = ...
      !Field2 = ...
      newID = !IDField
      .Update
      .Close
   End With

Does that help?

 

[mjdemaris]

Kind of. I was trying to set the value of a control on the form to the New ID, but I used a variable as suggested, and used two separate recordsets, and now I can fill out a Request.

Now, to see if I can copy the data from the search form over into the line item subform.

Here is the code that does the work for the form and subform:

Private Sub GetRequestData(x As Integer)
    Dim db As DAO.Database
    Dim tdf As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim newid As Integer
    
    Set db = CurrentDb
    
    Set rst = db.OpenRecordset("qRstRequestTest", dbOpenDynaset, dbSeeChanges)
    Set rst2 = db.OpenRecordset("qRstDetailsTest", dbOpenDynaset, dbSeeChanges)
    Debug.Print Me.subOrderDetails!PartNumber
    Stop
    
    With rst
        .AddNew
        !StatusID = Me.txtStatusID.Value
        !UserID = Me.txtUserID.Value
        !SupplierID = Me.cboSupplier.Value
        !CreationDate = Me.txtCreatedDate.Value
        !CreatedBy = Me.txtCreatedBy.Value
        !DeliverTo = Me.txtDeliverTo.Value
        !QuoteNumber = Me.txtQuoteNumber.Value
'        !TrackingNumber = Me.txtTrackingNumber.Value
        !WantedOption = Me.grpWantedOption.Value
        !WantedDate = Me.txtWantedDate.Value
        
        newid = .Fields(0)
        
        If x = 1 Then
            !ForStock = Me.chkForStock.Value
        End If
        If Not IsNull(Me.txtSpecialInstructions) Then
            !SpecialInstructions = Me.txtSpecialInstructions.Value
        End If
        If Not IsNull(Me.txtTitle) Then
            !ReqTitle = Me.txtTitle.Value
        End If
        
        .Update
        Debug.Print rst.AbsolutePosition
    End With
    
        rst.Close
        Stop
        
            With rst2
                .AddNew
                !RequestID_FK = newid 
                !PartNumber = Me.subOrderDetails!PartNumber
                !Description = Me.subOrderDetails!Description
                !Quantity = Me.subOrderDetails!Quantity
                !Cost = Me.subOrderDetails!Cost
                !DetailsStatusID_FK = 6
                !TrackingNumber = Me.txtTrackingNumber.Value
'                !ExtendedCost = (!Quantity * !Cost)
                .Update
            End With
    
ExitHere:
    db.Close
'    rst.Close
'    rst2.Close

' Trying to close these recordsets gives an error that the objects are not
' valid/no longer set.  not sure why

    Set rst2 = Nothing
    Set rst = Nothing
    Set db = Nothing

End Sub

 

[mjdemaris]

Would SQL work better? If so, how would i go about it?

 

[MarkK]

Does this work? Would SQL work better for what?

 

[mjdemaris]

This works for the first part - form and subform. But I haven't worked on copying data from search form to subform yet.

The example CJ gave and your re-written snippet use SQL. Would that be a better option? I'm not sure how I would pull the data from the controls with the examples given above.

 

[MarkK]

I think using a recordset to add records is simpler in some ways because it's more self-evident what is going on. The recordset is opened, the .AddNew method is called, then field values are assigned, and so on. I think the insert query is more reliable and probably faster if you had a lot of records to insert at once, but looking at what you're doing and how you are coding, I would use a recordset to insert rows.

A couple of observations...
In this line of code you are opening a recordset using a named query...

    Set rst = db.OpenRecordset("qRstRequestTest", dbOpenDynaset, dbSeeChanges)

I would be tempted to show that actual SQL in the code. That way everything is shown, and some future developer will not have to drill down and find your query in order to understand what is going on here. Also, this line of code...

        newid = .Fields(0)

Since we can't see the SQL we don't know what field .Field(0) is. I would be tempted to use the fieldname here, not this indexed member of the fields collection.

Given that you have all this code...

        !StatusID = Me.txtStatusID.Value
        !UserID = Me.txtUserID.Value
        !SupplierID = Me.cboSupplier.Value
        !CreationDate = Me.txtCreatedDate.Value
        !CreatedBy = Me.txtCreatedBy.Value
        !DeliverTo = Me.txtDeliverTo.Value
        !QuoteNumber = Me.txtQuoteNumber.Value
'        !TrackingNumber = Me.txtTrackingNumber.Value
        !WantedOption = Me.grpWantedOption.Value
        !WantedDate = Me.txtWantedDate.Value

... have you considered just using a bound form? It looks like you are going thru a lot of work here, but what is that advantage of this? It seems like, looking at your code, that you could probably eliminate all this code if you bind the forms. If you have some other process that would add records to these tables, we could open a recordset and add that data directly without using forms at all. Maybe that would be a better solution that building this all as unbound.

hth

 

[mjdemaris]

You are correct that this is a lot of extra work. Perhaps the question I should be asking is how would you recommend going about this task:

(Some background info)
The application opens - gets user name, dept id, user level

the navigation form opens - the default form displayed shows a listing of all Requests by dept.
Additional nav buttons provide forms to search for various Requests, search by Vendor, provide some reports.

Then, I can create a new Request or click on a field in the DS and open an existing form. If I want to Request an item previously requested/ordered, then I click a button on the Request form that opens a Search form. Here, I can click check boxes to select what I want (one at a time - at the moment), then add them to the line item subform on the Request form.

Using bound forms worked fine except when I wanted to add items from the search form. I couldn't figure out how to do that - even added the records to the table and tried to requery the subform. I couldn't add to the subform directly because of the FK - and I think it said something about a related record in the Request table not available.

One more bit of info: the search form uses linked tables from another backend.

I don't have a stripped DB to upload - maybe I should look into that...
I could take some screen shots for you.

Hope that description makes sense.

 

[mjdemaris]

Here is my first attempt at a stripped DB. You may have to enter a supplier before entering part number info.

 

[MarkK]

OK, so what form do I open? What buttons do I click on? I can find the code you posted in form fRequest1, but if I try to create a request the GetCurrUserID() function fails. TempVars!tmpEnviron has no value.

In general I would say it's excessively complex to not bind the form. And you are saying that you want to add a record from the search form, but I don't see how having this form unbound makes adding a record from the search form easier.

 

[mjdemaris]

Once you open it and stop macros, enable content, the hidden form should open, then the main form. If you click on the New Request button, that will allow you to fill out the Request form.

The search form is opened by clicking on the Add Item button.
Click any check box, click Add to Req, then Done.

That info is supposed to make it to the subform, but I am not sure it will with the copy you have, because I've been working on the recordsets for the unbound forms.

 

[mjdemaris]

If you are using windows, you should have a user name, retrieved by a call to some api.

fOsUsername is the function name, in one of the modules.