How to open a form in "add-only" mode

[sueb]

I want to have a separate form for adding records to my database (so that I can lock the main ID field and prevent users' accidentally changing it). I've been able to create this form, and put a button on my main form that opens it, but I haven't figured out how to make the new record available in the still-open main form: I have to close the main form and re-open it before I can successfully search for and find the newly-added record.

How do I do this?

[MikeTheBike]

Hi

You need to requery the 'MainForm' from the 'AddOnly' form before/during closing it. This can be done with this syntax

Forms("MainForm Name").Requery

This code can be used in the AfterUpdate, Unload or Close Event of the 'AddOnly' form.

One tip, if the 'AddOnly' form can be open/used with the 'MainForm' closed, then you will need to check that it is open before trying to Requery, ie.

If CurrentProject.AllForms("MainFormName").IsLoaded Then Forms("MainFormName").Requery

(applies to A2k or later)
HTH

MTB

[sueb]

Thanks, Mike! This is almost the entire thing! I do, however, have a Collection of Forms, and I don't know how to iterate through that collection in order to requery each one.

Here is the code that populates the collection:

Function OpenAClient()
    'Purpose:   Open an independent instance of form frmClient.
    Dim frm As Form
    
    'Open a new instance, show it, and set a caption.
    Set frm = New Form_Patient_IUR_Overview
    frm.Visible = True
    frm.Caption = frm.Hwnd & ", opened " & Now()
        
    'Append it to our collection.
    clnClient.Add Item:=frm, Key:=CStr(frm.Hwnd)

    Set frm = Nothing
End Function

and here is the code that closes each one:

Function CloseAllClients()
    'Purpose: Close all instances in the clnClient collection.
    'Note: Leaves the copy opened directly from database window.
    Dim lngKt As Long
    Dim lngI As Long
    
    lngKt = clnClient.Count

    For lngI = 1 To lngKt
        clnClient.Remove 1
    Next
End Function

I'm assuming that the "closer" iterator is much like what I'd need to do, but I'm not sure how to address each one for requery.

[sueb]

Anyone else have any ideas about this iteration problem?

[ADezii]

If I am correct, you are talking about something much more complex, namely Requerying Multiple Instances of a Base Form. If this is True, then all Instances of the Form would be contained withing the Collection, so try:

Dim lngKt As Long
Dim lngI As Long
Dim frm As Access.Form
    
lngKt = clnClient.Count

For lngI = 1 To lngKt
  Set frm = clnClient.Item(lngI)
    frm.Requery
Next

[sueb]

Naturally, you ARE, in fact, perfectly correct! This works like a dream, and is just what I needed. Thanks again, ADezii!

[ADezii]

You are quite welcome, sueb.

[sueb]

Oh, yes, there was one more detail, if anyone can help me with this. The whole point behind this separate form and the requerying thing is that the user needs to add a record to the "master" table, so I'd like to open this separate form in such a way that only an addition is allowed (or, at least, so that the record presented to the user is the blank one you get when you specify addition v. a record of an existing patient). The form's "Cycle" property is currently set to "All Records", but the only other options are "Current Record" and "Current Page", neither of which is what I want. Am I right about that?

I guess what I'm really wanting is to disable to standard "add record" button at the bottom of the window and replace it with my own version. As background, the reason I'm trying to do this is to prevent users from changing the sort of main identification field (the medical record number), which has happened several times from users who don't understand well how to use the "Find" command. I'm thinking to lock that field, but that means I have to provide a way to add a new patient outside the usual form,right?

[ADezii]

Is this 'separate form' a completely independent Form, or an Instance of existing Form in the Collection?

[sueb]

It's a completely separate form.

[ADezii]

Dim strFormName As String

strFormName = "<Your Form Name Here>"

DoCmd.OpenForm strFormName, acNormal, , , acFormAdd

[sueb]

ADezii, you are like some kind of Magical Answer Machine! I surely wish there were a way to manage some kind of real payback for all the help I find on this site. Just saying "thanks" seems so thin...

[ADezii]

Just having Users like you, who can take whatever knowledge that we can offer, and apply it successfully to your own individual scenarios, is enough reward in and of itself.