Subform needs to open on first record

[hilian]

I have a series of combo boxes on subforms whose control sources are fields in a table that has a many-to-one relationship to the main case-information table—meaning that there will likely be several records for any case. I need each combo box to open onto the first record for each case rather than create a new record. I’ve seen code that does this, but under somewhat different conditions, and my VBA skills are too rudimentary for me to be able to adapt the code to my needs. Can anybody help?

Thanks

Henry.

 

[CJ_London]

first/last/previous/next has no meaning in databases without an order of some sort - might be a date order, numeric, alphabetical etc.

So you need to clarify the order first

Then post the code you have tried and explain why it doesn't work the way you want.

I need each combo box to open onto the first record for each case rather than create a new record

this does not make sense to me, comboboxes to not create records

 

[arnelgp]

Please give more detail and if possible upload your trim down version of your db.

 

[hilian]

CJ_London, I found the following code that seems as thought it would do what I need,

2_syn_1 subform.SetFocus
DoCmd.GoToRecord , , acFirst

My subform is called 2_syn_1 subform. It’s one of a number of subforms, each containing a combo box, that I’m using to enter data into a table related (many-to-one) to my main table. I want to subform to open on the first instance of the primary key for a case.

I haven’t tried the code because I’m stuck on the instruction to use the AfterUpdate event of the primary key control on the main form. I can find the AfterUpdate event for the main form, but I’m confused about the AfterUpdate event of the primary key control.

Arnelgp, Thank you for offering look over my db. I tried to upload it, but I got the message that it exceeds the maximum size limit. Let me see how I can simplify it.

 

[CJ_London]

I tried to upload it, but I got the message that it exceeds the maximum size limit

try zipping it

CJ_London, I found the following code that seems as thought it would do what I need,

2_syn_1 subform.SetFocus
DoCmd.GoToRecord , , acFirst

My subform is called 2_syn_1 subform. It’s one of a number of subforms, each containing a combo box, that I’m using to enter data into a table related (many-to-one) to my main table. I want to subform to open on the first instance of the primary key for a case.

As previously advised - you need an order so what does 'first instance of the primary key' mean? Being a primary key means it will be populated so it will find the first random record. Do you mean the lowest primary key?

 

[hilian]

I attached a zip file of the db. At this point, I am testing using made up data, which is what you will find in the tables.

There are two related tables for entering data named MainDataQPR2 and DropdownDataQPR2 (QPR2 is the name of the project). The other 26 tables feed dropdown lists. Because the fields that use the dropdown lists are mostly in the DropdowDataQPR2 table and are scattered though the data entry process, I have had to use a single subform for each field. Each subform contains a combo box whose row source is one of the 26 tables.

What I'm trying to do is this: the current record on the main form will have several related records on the subform. I would like to be sure that that each subform opens at the first of these subsidiary records. When I said first instance of the primary key, I meant first instance of the current case--first instance of the number in the primary key field that corresponds to the current case.

 

[CJ_London]

When I said first instance of the primary key, I meant first instance of the current case--first instance of the number in the primary key field that corresponds to the current case.

So far as I can see that is what it is doing. You need to give me a specific example from the data you sent of what it is showing now and what you want it to show.

I suspect your data is not normalised which is making your job a lot harder, but I don't understand what it is required to do so cannot advise

 

[hilian]

In trying to describe what I believe is happening, I discovered a different problem that is potentially more serious.

First, an instance of the original problem: in the table, DropdownDataQPE2, the field 1_S-gui_1_a has an entry on record 9. This entry belongs to case 2, as indicated in the field FKMainData. The first instance of case 2 is record 4, for which this field is blank. What I would like to have happen is for the entry for this field in record 9 to appear in record 4 instead. There are several more instances of this, particularly in the fields 1_pro_2a and 2_pro_1_a. These two fields are fed from sub-subforms on a subform, so the problem might be with the subform, frmProbing subform.

The new problem I discovered is that although the subforms are connected to the main form on two fields, FKMainData and IDNo—the tables are related on Primary key, MainDataQPR2 and FKMainData, DropdownDataQPR2—for some records only IDNo has data and not FKMainData, for other records it’s the opposite, and some records have nothing for either field. Since these are the fields I’m using to create data sets for analysis with SPSS, they have to be populated for each record.