A question for Microsoft Access experts

Factual Questions
1

I’m designing a database application in Access. I’m trying to do something that seems simple on the surface, but I can’t for the life of me figure out how to do it cleanly. I’m pretty good with Access, but not only am I not an expert I’m also self-taught, which means there are a lot of little things I don’t know about. Can someone help?

What I need to do is very straightforward, to wit: The user is working with data on a form; the dataset is a list of people. I want the user to click a button to call up a separate form with a separate function, one that shows a totally different set of data for the same group of people. Here’s the trick: I want the new form to pop up with the same person active as on the first form. I want all the same data available, but I want to match the active record from the first form and jump to that same person on the second form when it appears.

I tried SQL WHERE, filtering, etc., and I couldn’t get them to work (probably because I’m doing it wrong). I also looked into matching record numbers, but I couldn’t figure out how to bring the first form’s number forward. (I recognize this could probably be done with Visual Basic, but I know very little about it, so I’m not able to create a module from scratch to do this.)

At any rate, I abandoned that and whipped up an ugly, kludgy macro that copies the name from the first form to the clipboard, opens the second form, and pastes the name into the built-in “find” function to locate that name. It works, but even with “echo off,” the modal “find” window still appears, which makes it an ugly and unprofessional-looking solution. If I can’t come up with anything else, I’ll leave it as is, because it does work as intended, but I’d really like to have something simpler and cleaner.

I’m sure this is one of those simple tricks Access experts use all the time, and I’ll probably be embarrassed at my ignorance if somebody can offer an easy, obvious alternative. But, really, to me, it’s preferable to face the embarrassment for a few minutes (and learn something cool) than it is to put up an expert facade and pretend the kludgy fix doesn’t bother me.

Anyone?

2

Don’t have a lot of time right now, but for starters: what version of Access are you using?

(there may be a wizard that can do this type of thing for you)

3

Use the command button wizard.

Create a command button by drawing on the original form.
Select FORM OPERATIONS & OPEN A FORM.
Select the form you want to open.
If the original form and the target form are based on the same data a window should appear that says “Do you want the button to find specific information to display in the form?”
Select OPEN THE FORM AND FIND SPECIFIC DATA TO DISPLAY.
Select the fields on both forms to use as the linking criteria.
Pick an image for the button or assign text.
Name the button.
Hit finish.

The code generated should look something like this:



Private Sub cmdYourButton_Click()
On Error GoTo Err_cmdYourButton_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "sfm_pc_stat_view"

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

Exit_cmdYourButton_Click:
    Exit Sub

Err_cmdYourButton_Click:
    MsgBox Err.Description
    Resume Exit_cmdYourButton_Click

End Sub

The SDMB of Access is here http://www.access-programmers.co.uk/cgi-bin/ubbcgi/Ultimate.cgi

4

I’ll take a feeble shot at this, Cervaise.
I think that all you need is to capture the name to a global variable, and then open your second form based on the global variable. For instance, say you are using a combo box, and on selection of the name, you capture it to a global variable:
sgName = me!combobox.namecolumn
where the sgName is declared as string in your Main module.

Then you put this code in the combo box’s afterupdate event, where you can use:
DoCmd.OpenForm “2ndformname”, acNormal, , “tableorquery.name = '” & sgName & “’”
to open your form.
HTH. If you need clarification, feel free to email.

5

Don’t know why the code option got screwed up. I forgot to add you may need to tweak your second form’s On Open procedure to get the focus set to the correct record. It may not be exactly what you need but it should help to get you there.

6

Thank you, OpenGrave, that’s exactly what I needed. And as I read your post, I slapped my forehead to remember that I had seen that option in the wizard, but never actually used it. Thanks for the reminder. I had to tweak the table definitions so the two fields would recognize one another, but it works perfectly.

Thanks again!

P.S. That’s not to say “neener neener” to BF and K364; it’s just that OG was the first to offer a complete solution. Thanks for your willingness to devote a few minutes to my pathetic little problem. :wink: