Hi All,
In my ignorance I don't know how to set 2 unlinked forms to the same record. I have designed a form that allows the user to input criteria that is displayed in a combo box via SQL: The combo box has multiple columns with the criterion TestID in the first column.
I now wish to move to the selected record on a separate form. I believe I will need to use a dataset clone and bookmark but have very little idea how to proceed.
Any help most welcome
Thanks
Skinny

Hi Skinny,

I use the following to open an unlinked form from a button on the main form:

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmCLNTMAIN"

    stLinkCriteria = "[ClientRefNo]=" & Me![ClientID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

where ClientID is a textbox on the main form. I assume you could just replace this with the appropriate parameter from your combo box.

Hope this helps,


Clive Astley

Hi Clive,
Thanks for that. That does the trick nicely.
Can I extend the question a little further? Is it possible to do the same thing ie open a form using vba as if it were a subform. In other words if there is an existing record with the same ID as the record on the main form it will open on that record, if not it will open a new record and set the ID to match that on the main form?
I realise that in normal circumstances this is best done using subforms but in my data base there are many linked subforms each with scores of controls. It would be much neater to open one form at a time.
Am I asking the impossible?
Thanks
Skinny

Glad it worked for you Skinny.

In doing what you have done you have pretty well done what you want to do in your expanded request.

Let us say you have two tables, Clients and Orders, keyed on ClientID.

Create your frmClients bound to table Clients. Create your frmOrders either bound to table Orders or set its recordsource to an SQL statement or query so you can show additional information.

Use the command button VBA you already have with the addition of a facility to save the current Client record (for those cases where you have just added a new client but it hasn't yet been saved to the Client table):

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

You may have to modify this command a bit if you are using other than Access97.

If you drop me a private email I'll send you the little test database I created before replying to you.

Best wishes,

Clive Astley