Making an Open Form Button using a Composite Key

[Sienna]

First of all, I found another thread here: https://www.accessforums.net/forms/c...key-38617.html, which has a very similar problem to mine. I tried to modify that solution to fit my situation, but I have been unsuccessful.



Some background information:
I work in a library, and I keep track of serials (magazines and journals) using an Access database that I built. I recently switched from a single primary key to a composite key. Serials all have identifying numbers (ISSNs) given to them by a particular organization. I import data from many different publishers, all of which include ISSNs, so it makes the most sense to use ISSNs as my primary key. The problem is that some serials have the same ISSN because of title changes, and some serials don't have any ISSN at all. Originally, I was using a primary key and adding a random letter to the ISSN to make the two entries different. In an effort to retain the integrity of the ISSN primary key, I decided to make a composite key, so that I could have a second field that allows me to have several records with the same ISSN number. This is very necessary, and for the most part, it is working beautifully.

The problem is that the buttons on our forms don't work anymore. I can't figure out how to base an open form button on a composite key. I have tried the solutions in the thread linked above, and a solution in another thread in another forum. The solutions are all similar, and I've tried various versions of all of them. Nothing works. I have a lot of Access experience--but in the GUI way. If I can avoid writing expressions and using SQL, I do. However, if you give me code, I can usually figure out how to use it.

Please use these field names so that I can tell what goes where:

For the form with the button: [Table1].[Primary Key1], [Table1].[Secondary Key1]
For the form the button opens: [Table2].[Primary Key2], [Table2].[Secondary Key2]

If there's anything in your code that is supposed to be replaced with a unique name from my database, please let me know.

Thanks for any help.

[orange]

Please show the code you have currently for the button click event.

In Relational database, each record in a table is uniquely identified by the Primary Key.

[Sienna]

Right now, it's a macro created by the wizard, an open form command with this where condition: ="[Primary Key1]=" & "'" & [Primary Key2] & "'"

I have tried using the expression builder to use the code in the link I posted above. I couldn't get any of those solutions to work for me.

[Sienna]

To give a little more information, currently my form has a button that will open a second form based on the first key. So, if there is more than one record with that key, the form that is opened will be filtered to only those records. I have added a drop-down search box so that the user can type in the secondary key to go to the relevant record. It adds an extra step, but it is an acceptable workaround. I would still prefer it if the button took the user directly to the correct record, though.

[MarvinM]

Right now, it's a macro created by the wizard, an open form command with this where condition: ="[Primary Key1]=" & "'" & [Primary Key2] & "'"

I have tried using the expression builder to use the code in the link I posted above. I couldn't get any of those solutions to work for me.

Hi Sienna,

It looks to me like you just need to modify your where condition from matching on one key to matching on two keys. You do this with an 'AND' operator like this:

="[Primary Key2]=" & "'" & Me.[Primary Key1] & "' AND [Secondary Key2]=" & "'" & Me.[Secondary Key1] & "'"

I swapped around Key1 and Key2 because you said "the form the button opens: [Table2].[Primary Key2]" and the key selected on the form with the button is [Primary Key1]. Hope I got that right.

(And a little friendly advice to make your life easier in the future: Do try to avoid using spaces in your names.)

HTH,
MarvinM

[orange]

Please show your table design in detail. I don't use macros so can not comment on them.

You have 1 PrimaryKey -now that may be a composite primary key (composed of many fields) - but its 1 PK.

You do not have PK1 and PK2. You may have fields called PK1 and PK2 and the composite PK1&PK2 is the Primary Key.

[Sienna]

Hi Sienna,

It looks to me like you just need to modify your where condition from matching on one key to matching on two keys. You do this with an 'AND' operator like this:

="[Primary Key2]=" & "'" & Me.[Primary Key1] & "' AND [Secondary Key2]=" & "'" & Me.[Secondary Key1] & "'"

I swapped around Key1 and Key2 because you said "the form the button opens: [Table2].[Primary Key2]" and the key selected on the form with the button is [Primary Key1]. Hope I got that right.

(And a little friendly advice to make your life easier in the future: Do try to avoid using spaces in your names.)

HTH,
MarvinM

Thank you for your response!

I was not giving the actual field names because it's embarrassing to say that my field names were like this [Proposed Primary Key]! Kinda clunky. I did that when I was first moving over from a Primary Key to a composite key. Not only that, but each field was called the same thing in the different tables--which makes it pretty much impossible to figure out which one is being referred to.

So, I changed the field names to this:

The first form, which contains the open form button is called "Cataloging Notices". The Primary Key fields are: [PrimaryKeyC] ; [SecondaryKeyC]
The form that is opened after pressing the button is called "All Holdings List". The Primary Key fields are: [PrimaryKeyA] ; [SecondaryKeyA]

These are the where conditions that I have tried. I switched the field names around, just in case I had it backwards.

="[PrimaryKeyC]=" & "'" & [Me].[PrimaryKeyA] & "' AND [SecondaryKeyC]=" & "'" & [Me].[SecondaryKeyA] & "'"

and

="[PrimaryKeyA]=" & "'" & [Me].[PrimaryKeyC] & "' AND [SecondaryKeyA]=" & "'" & [Me].[SecondaryKeyC] & "'"

Both attempts return this error:

The object doesn't contain the Automation object 'Me.'

You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

Check the component's documentation for information on the properties and methods it makes available for Automation operations.

When I click OK, another box comes up titled "Macro Single Step," with the following text boxes:

Macro Name: CATALOGING NOTICES : Command43 : OnClick : Embedded Macro

Condition: (blank)

ActionName: OpenForm

Arguments: ALL HOLDINGS LIST, Form, , ="[PrimaryKeyA]=" & "'" & [Me].[PrimaryKeyC] & "' AND [SecondaryKeyA]=" & "'" & [Me].[SecondaryKeyC] & "'"

Error Number: 2950

There are three buttons. "Step" and "Continue" are grayed out. "Stop All Macros" can be clicked.

Should I be doing something like replacing the Me with the table name?

Thanks!

ETA: I looked up error 2950, and found a help page from Microsoft saying that my database is not trusted. http://support.microsoft.com/kb/931407 I opened the security center, though, and the file location is already trusted. I had done that ages ago.

[Sienna]

Please show your table design in detail. I don't use macros so can not comment on them.

Is there a place I can cut and paste my table design to show it to you? What kind of information are you looking for? Which table are you referring to? My database is pretty massive.

You have 1 PrimaryKey -now that may be a composite primary key (composed of many fields) - but its 1 PK.

You do not have PK1 and PK2. You may have fields called PK1 and PK2 and the composite PK1&PK2 is the Primary Key.

Thank you for the clarification. I understand the distinction, but I prefer to use the terms primary and secondary for the 2 parts of my Composite Key. At least for now.

[MarvinM]

Hi Sienna,

You are talking about a form. Right? The first form is called "Cataloging Notices". And there is a button on the form that you want to click in order to open a second form, "All Holdings List". Am I correct so far?

The form does not have a primary key. The form has a Record Source, usually a table. That table would have a primary key. On the form are controls. Controls can be buttons, like the one you click to go to the second form. Controls can be text boxes, like those that might show the data in your table's fields.

In the expression, Me.PrimaryKeyC, "Me" (no brackets) represents the form, and "PrimaryKeyC" represents the control on the form. The field in the table may be named PrimaryKeyC, and the control on the form may be named something else. You need to look at the form in design view, and examine the properties of the control to see if it is named the same as the data field from the table.

[Sienna]

Thank you! I didn't realize that I needed to use the names of the controls instead of the names of the fields in the underlying table or query.

I've found the names of the controls and changed the macro where line accordingly, but I'm getting the same error message that I quoted above. I'm going to keep on working on it.

[Sienna]

Okay, I created another button with this macro where condition:

="[PrimaryKeyA]=" & "'" & [PrimaryKeyA] & "'" And "[SecondaryKeyA]=" & "'" & [SecondaryKeya] & "'"

I changed the names of the controls. Since the Cataloging Notices form is showing fields from the All Holdings List table (via a query), both of the controls are a's instead of c's.

This time there's no error message, but it's not finding one record. It's just opening the All Holdings List form with all of the records.

Thanks!

[Sienna]

Anybody have any ideas how I can fix this?
Thanks.