Add Matching Record on Filtered Form

Hey NeoPa,

Just to clarify, you're trying to insert new records into the Many to Many table? Both keys must exist in the corresponding One tables. Also you can't insert just one key into a Many to Many table you need to do both. Correct me if I'm wrong about the Many to Many relationship.

If I'm understanding this correctly you want to be able to filter the form by job. After filtering if the user chooses to add a new entry you want it to automatically be associated with the job the form is filtered by.

I think the simplest solution would be to add a new control for [Job]. But you seem to want to avoid that.

Have you considered changing your design? Alternatively what about creating a form based on tblJob that has a continuous subform based on tblJobCust that would list all customer entries. Allow the user to add customers to the job through the subform. Each page of the form would essentially be filtered by job.

1. The table, though not by definition a Many-to-Many, would support that structure so yes, that is what I'm talking about. I do appreciate why the error message is ocurring - it's the resolution I'm after.
2. Right on the button.
3. I do want to avoid that. Not because it is a bad solution, but because if I'm maintaining a number of such forms the extra control is an extra item that can go wrong (if not properly maintained). There are a number of similarly logical reasons why I'd prefer to do it without that, not least of which is that if systems don't fit the logical structure they're supposed to represent then they are naturally more difficult to work with (they introduce more items that must be remembered specifically).
   Having said that, I will use that approach if I fail to find a more 'elegant' solution.
4. I didn't give this idea much more than fleeting consideration mainly for the same reasons specified in my last paragraph.

1. The most snugly fitting solution.
2. To learn the fundamental concepts (of this particular issue) so that I will have the understanding and experience I need, should I come across similar issues in future.

I rarely use forms to manage my data so I expect this is easier than I found it (I hate it when OPs say that :D)

I have a simple table (laid out below) with lists of customers for various jobs that have to be run. The PK is a combination of the first two fields ([Job] & [CustNo]) and I will typically want to maintain the various (logically separate) lists in different sessions (I may well use the same form design but only show one job at a time).
For instance, when working on the 'ElecInv' job, I will filter to show only those records with [Job]='ElecInv'.
The form design is simply a [txtCustNo] control (linked to the [CustNo] field) on a continuous form.

Table Name=tblJobCust

Expand|Select|Wrap|Line Numbers

1. Field; Type; IndexInfo
2. Job; String; Composite PK
3. CustNo; String; Composite PK
4. Expiry; Date/Time    'Not necessarily required

My problem is that, although the form filters the existing records perfectly, when I try to add a new record (simply by entering the [CustNo] data) it complains about a missing [Job] field (ErrMsg=Index or primary key cannot contain a Null value). Clearly the [Job] field is not being set up to match the filter as I had (forlornly) hoped.

What do I need to do to add that value into the field? I'm hoping to avoid setting up invisible controls on the form. I expect I need to add the code in the Form_BeforeUpdate() event procedure, but exactly what I need to refer to I'm afraid I don't know. I also don't know which checks I may need to do to ensure this code is only run when a new entry is added and not when an existing record is amended or deleted.

Hi, NeoPa.

As far as I've got it, your form is "main form" but you are expecting subform like behaviour from it when it is filtered. I suggest you to emulate this behaviour by changing control.DefaultValue to filter value. If you've designed button for the form filtering this will be simple, if you use Access filter buttons you may handle Form_ApplyFilter event and parse fieldname to be filtered by and filtervalue from Form.Filter.

Regards,

Fish

I completely understand. I had actually just done a small form with a Many to Many relationship for my boss last night. It was something quick and dirty but it sounded like it might suit your problem. It was an event scheduler that all the office assitants could use to "sign" their superior up for an event.

In your case the events would be jobs and the attendee list would be a customer list. There is little coding involved.

I'm going to attach a picture cause it explains things better than I can.

Many To Many

Fish,
Thanks for your response.
I was more hoping for subform behaviour than expecting it ;) I would have been a little surprised to find it if truth be told, but it illustrates clearly what I need at least.
A default value, while a good idea in some respects, would be more complicated than I'm looking for if it were tailored to each use, and mostly the wrong setting if not. In similar vein to my response to JKing earlier, I repeat that I'm not looking for a way around this issue. I'm hoping to gather the tools to handle this subject flexibly rather than an individual way around a specific problem.
How do I determine (in the Form_BeforeUpdate() procedure) what type of update is in process?
How do I (is it even possible without a dummy/invisible control) update the other key field in the record as Access is about to apply my changes from the form.

Again, thanks for your efforts which truly are appreciated :)

• code is more or less context independant
• it works so far with filter expression like "Expr1 AND Expr2 AND ... AND ExprN" taking ExprN into account when it is an exact comparisson
• Control.Tag property may be used instead colFilterAppliedTo collection
• the main idea is to parse fieldnames and filtervalues from Form.Filter and find relevant controls, form controls or table fields may be easily updated on Form_BeforeUpdate via Control.Value property or running SQL "UPDATE..."

Hi NeoPa,

Not knowing a little more about your setup, I'm not sure if this is on the right track or not, but... If you are filtering the form based on the job number/type, how are you doing so? If on open, can you grab the Me.OpenArgs (which nec must include the job number that you are filtering on), and use it to populate the job part of your composite key using an INSERT INTO ... VALUES... sql update query (run as you indicated in the Before_Update event of your form)?

Regardless of whether you are able to use the openargs, or need to grab the job number from a combo box, etc, you will probably find the insert into update query is the easiest way to complete the composite primary key with it's job counterpart...

Regards,
Scott

p.s. I'll be looking back in on this with interest to see what you come up with!

1. I want a way (to know how to) of accessing the actual record buffer of the form without going via the controls of the form (if this is even possible).
2. I also want a way to determine (within the BeforeUpdate event procedure) which type of update is in process (Edit or Add).

Hi, NeoPa.

I'm somewhat surprised with your answer.
The idea is to retrieve Form.Filter expression, parse fieldnames and filtervalues from it and find corresponding controls. Updating fields filtered by via Control.DefaultValues is only one possible (and to my mind the most suitable) option.
Anyway, I've written some code.
...

Hi Adrian,

see this link:

http://support.microsoft.com/kb/210334

pDog

Did I miss something or couldn't you just use something like the following to accomplish your goal.

Expand|Select|Wrap|Line Numbers

1. Option Compare Database
2. Option Explicit
3. Dim GlobalFormVar As String
4. Private Sub Form_Current()
5. If Me.NewRecord = True Then
6.  

Then on form filter or form open set the variable and it will contain the valu8e when you need it.

Den,
I'm not sure if your code was chopped intentionally or not, but the Me.NewRecord is certainly the answer to one of the questions I was asking :) The Form_Current() may also be the place to use it in place of the BeforeUpdate() procedure I'm currently using. I'll see how it fits in.