Unlinked Forms (Rafe?)

[Carenne]

Rafe, hope you're catching this. You mentioned in a response to one of my other posts that you often use unlinked forms. I'm wondering how you get all the data linked correctly when you do that. I've had occasion when it made sense to me during a certain phase in the form's creation to do that, but then I would run into a snag somewhere and assumed I trying to do the impossible so I took another route. Can you give me an example of when and how you have accomplished that? I'm most curious. Thanks! --Carenne [sig][/sig]

 

[rafe]

Carrenne,

First, thanks for addressing this post to me but... there are many REAL gurus out there that are farther up the programmer-evolutionary scale (MichaelRed is in that list along with many others). FYI: I'm at the stage of migrating from worksheets to forms. SO TAKE MY ADVICE AS AN EQUAL NOT AN EXPERT!

Now to answer the question...

For MANY applications Access 2K can do most of the work. However, sometimes all of the automation with limited ability to get at it can get actually slow you down -- or maybe I don't know enough yet. This translates to doing work-arounds to get the form to behave exactly the way you want.

What I've been doing is using accept/cancel buttons on the forms (usually header/line item type). I've done this two ways so far.

1) I've created a buffer file that is (yes linked) to a sub-form for the line item data. Upon clicking the accept button I validate data & inter-field relationships & write the data in to the file using a record set. Header goes 1st then line item data goes in sequentially—reading data from the buffer & just moving it over. This still lets Access do most of the work but sometimes can get restrictive (read work-arounds). You’ve also got to worry about buffer file housekeeping (NBD there).

2) Now for the un-linked form: Even #1 has sometimes gotten too cumbersome (depends on how you define it, I guess). Now I often buffer things in a memory array & write to the file directly. A wee bit more work & ALL of the programming goes into one module but total flexibility.

Of course, whenever possible I try & let Access do the work!

If you’re interested, ask, & I’ll share the little knowledge I’ve gleaned so far.

rafe
[sig][/sig]

 

[rafe]

Oops! forgot to insert some code to the last post.

The linking happens on the writes. The "Real" tables are linked with referential integrity set. That's why header goes in before line items.

'form globals
Dim rsDoc As New ADODB.Recordset 'Bad form to do "New" here
Dim rsPay As New ADODB.Recordset 'Bad form to do "New" here
dim DocIDSave as long

Private Sub Form_Open(Cancel As Integer)
    rsDoc.Open "Documents", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adLockOptimistic
    rsPay.Open "Payments", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adLockOptimistic
End Sub

Private Sub gen_Click()
   'validate record as a whole
   addHeader
   'loop thru either memory buffer or file buffer
   'for/while NOT eof or at end of buffer
      addLine
end sub

private sub addHeaders()'After button click & validations
    rsDoc.AddNew
    'the DocID primary Key is AutoNumber
    rsDoc!PayeeID = selPayID 'from a form's control
    'More fields are set like above
    rsDoc.Update  'Post your changes
    DocIDSave = rsDoc!DocID 'For linking line items
End Sub

Private Sub addLine() 'After button click & validations
    'PayID primary key is autonumber too
    rsPay.AddNew
    rsPay!DocID = DocIDSave   'link to the header
    'set fields
    rsPay.Update
End Sub

hope this is more direct.

rafe
[sig][/sig]