Creating an open form button using a composite key

[dhollingsworth]

Hi,



I am trying to create a button on a form which opens a second form and finds data based on the contents of the first form. However the table linked to the first form uses a composite key (Asset_ID and Criteria_ID). When I create a button access asks me which fields in each form are common so that the values can be referenced, but it will not allow me to more than one field from each table (both tables have asset_ID and criteria_ID in them).

I assume that want I want to achieve can be done using the expression builder but the syntax is getting the better of me. Can someone point me in the right direction?

Many Thanks,

David

[ItsMe]

This should work as long as the Id's are numbers and not Text data type.

Dim stLinkCriteria as String
stLinkCriteria = "[Asset_ID]=" & Me![Asset_ID] AND "[Criteria_ID]=" & Me![Criteria_ID]
DoCmd.OpenForm "OpenFormName", , , stLinkCriteria

[dhollingsworth]

Hi ItsMe,

Thanks for the response. There is a problem, Asset_ID is a text string unfortunately. It comes from trying to create a database around an existing list. It isn't how I would have set it up.

Dave

[ssanfu]

Then try:

Dim stLinkCriteria as String
stLinkCriteria = "[Asset_ID] = '" & Me![Asset_ID] & "' AND "[Criteria_ID] = " & Me![Criteria_ID]
DoCmd.OpenForm "OpenFormName", , , stLinkCriteria

Strings must be delimited with single quotes or double quotes.

Is Me![Criteria_ID] string or number?



BTW, there was a missing "&" after Me![Asset_ID]

[ItsMe]

I put it in a couple lines. This way you can step through it. Also, you can add more lines (fields) of criteria.

Dim stLinkCriteria As String
stLinkCriteria = "[Asset_ID]='" & Me![Asset_ID] & "'"
stLinkCriteria = (stLinkCriteria & " AND ") & "[Criteria_ID] = " & Me![Criteria_ID]
DoCmd.OpenForm "OpenFormName", , , stLinkCriteria

[dhollingsworth]

Hi,

Thanks for the responses. In response to one of the questions above, Asset_ID is a text string and Criteria_ID is an integer. I'll try the code provided when I'm in the office tomorrow.

Dave

[dhollingsworth]

Many thanks for the replies. the code provided worked a treat!

Dave

[ItsMe]

Glad to hear. You can save that code as a snippet. With a couple additions, it makes foe a good start to a search form.

[dhollingsworth]

Hi,

The previous code worked great for search and updating existing fields, however I now want to use it to create new records. Its the same fields and tables etc. as before, but rather than just finding data based on the values of Asset_ID and Criteria_ID, I want to add data to the linked table based on the values of these fields in the first table. i.e. I need to be able to open the form as a subform in a new window with linked master and child fields. Can anyone advise how to do this?

Any help greatly appreciated.

Dave

[ItsMe]

What code are you trying to add to? Can you post it here? What do you mean by "open a subform in a new window?

If you are opening a new window, won't it be a Main form?

[dhollingsworth]

Hi ItsMe,

Yes you are right. The form is presently a main form, but I would like it to act as a subform. I have another main form which displays records in continuous format, each record has a button to enable multiple records for that record to be added, so it needs to act as a sub form with with linked master and child fields. I don't have any code as yet, I was hoping it would be straight forward but may be not?

Dave

[ItsMe]

Maybe someone else has another approach but.... when I come across an instance where I have to add records to a subform, I use VBA to UPDATE or .Addnew. As for opening another form, that is what the code posted here does. The main form manages the rest with the Main Link Child Link thing.

So not sure what the question is. Are you opening a form based on criteria? Are you adding records to a child recordset?

When I add records to a subform, I do it programmatically. Then I requery the subform, if needed. I don't see any reference to code that will add records here in this thread.

[dhollingsworth]

My VBA is not very good. Basically, access 2007 doesn't seam to allow a subform to be created where you are using a continuous form, therefore I'm not able to set up the parent/child links in the normal way. I've attached two images of the forms in question. The first form "Asset Condition Scoring" has a button for each record which allows defects to be recorded against each criteria. The button opens "Asset Defects and Remedial Works" form, each criteria on "Asset Condition Scoring" form can have many defect records. I don't know how to get the parent child ID fields to link between the tables which contain the data on these two forms. ANy help really appreciated.

Dave

[ItsMe]

Identify the following
Name of the continuous form you want as a subform
The field name on the subform for Child Link
Query or Table name to be recordset for Main form
Field name for Main Link on Main form

Just for practice....
Create a new form without the wizard
Set the from's recordset to be the apropriate Main form table or query
Place one control on the Main form, the control should be bound to the field that will act as your main link.
Save your Main form, something like "A_frmTest"

Turn the wizard on
Place a subform control on your new main form
Tick "Use an existing form" from the wizard
Select your continuous form 's name
Click next
Tick "Define my own" to choose Main and Child
Then use the pulldowns to select the field names
Click next
Click finish

That should give you an example of the correct main form with child link subform

[dhollingsworth]

Hi ItsMe,

Thanks for the response, info as follows:

Name of the continuous form you want as a subform Its the other way round, a button for each record on the continuous form needs to open a single subform in a new window; The "Defects Info" button on the "asset condition scoring" form needs to open "asset Defects and remedial works" form (see previous post attachments). The name of the sub form is: frmASSET_WORKS.


The field name on the subform for Child Link It uses a compound key, the fields are "Asset_ID" and "Criteria_ID"
Query or Table name to be recordset for Main form Its a query; "qryASSET_CONDITION"
Field name for Main Link on Main form The same as Child Link i.e. "Asset_ID" and "Criteria_ID"

I hope this makes sense, I'm not sure how to explain it without sending the file over to you.

Dave