Add Matching Record on Filtered Form

[NeoPa]

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

[i]Field; Type; IndexInfo[/i]
Job; String; Composite PK
CustNo; String; Composite PK
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_BeforeUpda te() 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.

[JKing]

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.

[NeoPa]

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.

JK,
Thanks for your response. I'll try to reply on a paragraph by paragraph basis.

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.

I appreciate there are work-arounds for this situation, but I'm looking for :

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.

Please don't think I fail to appreciate your efforts. That is far from the truth. I just thought you deserved a clear explanation of why they're not quite what I'm after.
Thank you again.

[FishVal]

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

[i]Field; Type; IndexInfo[/i]
Job; String; Composite PK
CustNo; String; Composite PK
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_BeforeUpda te() 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.Default Value 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_ApplyFilte r event and parse fieldname to be filtered by and filtervalue from Form.Filter.

Regards,

Fish

[JKing]

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

[NeoPa]

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.Default Value 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_ApplyFilte r event and parse fieldname to be filtered by and filtervalue from Form.Filter.

Regards,

Fish

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_BeforeUpda te() 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 :)

[NeoPa]

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

Believe me when I say I'm not afraid of code or complexity.
What I need just now though, is to make a small - non disruptive change to an existing set-up. I'm hoping to have a template for further updates to my existing systems to make them more user-friendly (idiot-proof ;)). Code is not a problem, but I am looking for a simple structure that is both obvious to use, as well as obvious to maintain when I come back after years without looking at it.
One further point. Please don't feel it's up to any of you people to find a solution for me. If you know the answer - that's fine. If not - all suggestions are considered, but at the end of the day, it's down to me to find the solution and if I have to dig through the help-system or the documentation in detail, then I will. If I find anything pertinent I will post of course.

[Scott Price]

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!

[FishVal]

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_BeforeUpda te() 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 :)

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.Default Values is only one possible (and to my mind the most suitable) option.
Anyway, I've written some code.
[code=vb]
Dim colFilterApplie dTo As Collection

Private Sub Form_ApplyFilte r(Cancel As Integer, ApplyType As Integer)

Dim strFilter As String
Dim intFilterCount As Integer
Dim strCmpExpr As String, strFieldName As String

'Filter is being removed
If ApplyType = 0 Then
For Each ctrl In colFilterApplie dTo
ctrl.DefaultVal ue = ""
Next
Exit Sub
End If

'Filter is being applied
Set colFilterApplie dTo = Nothing
Set colFilterApplie dTo = New Collection

'Debracketing Access filter string, assumed fieldnames and filtervalues
'doesn't contain brackets
strFilter = Replace(Me.Filt er, "(", "")
strFilter = Replace(strFilt er, ")", "")

intFilterCount = UBound(Split(st rFilter, " and ", , vbTextCompare)) + 1

For i = 0 To intFilterCount - 1

strCmpExpr = Split(strFilter , " and ", , vbTextCompare)( i)
strFieldName = Split(strCmpExp r, "=")(0)

On Error GoTo SkipControl
For Each ctrl In Me.Controls
'try to raise error on ctrl not supporting
'ControlSource or DefaultValue
If ctrl.ControlSou rce = ctrl.DefaultVal ue Then
End If
If ctrl.ControlSou rce = strFieldName Or _
Split(strFieldN ame, ".")(1) = ctrl.ControlSou rce Then
ctrl.DefaultVal ue = Split(strCmpExp r, "=")(1)
colFilterApplie dTo.Add ctrl
End If
Continue:
Next
On Error GoTo 0

Next i
Exit Sub

SkipControl:
Resume Continue

End Sub

Private Sub Form_Close()
Set colFilterApplie dTo = Nothing
End Sub
[/code]

Code features:

• 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 colFilterApplie dTo 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_BeforeUpda te via Control.Value property or running SQL "UPDATE..."

[NeoPa]

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!

Hi again Scott.
I appreciate your interest and the effort you've put in to help. As I've tried to express before though (tried being the operative word here - it's a subtle point that's not easy to pass on in words) my interest is to use Access methods better, rather than to try to get around them. I could put in some SQL (and I'm certainly already using the Me.Filter string to get my [Job] code)

'Typical Me.Filter value = "[Job]='ElecInv'"
strJob = Split(Me.Filter, "'")(1)

... but I would see that as getting around Access rather than using its features. I just find the features need a boost sometimes ;)
I am just starting to branch into using the forms to manage data in an interactive way (I've used forms before but mainly unbound, and simply as a way to control how a job (code) is run).
Going back to post #6 :

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).

My best way so far, which is also what Jared suggested, is to include an extra, invisible, control (I've called txtJob). When this value is Null in my BeforeUpdate event procedure, I know that it's adding a new record. This works. This is not exactly as I'd like to design all my future record update forms as it means including an (or sometimes more than one) extra, invisible, control on the form. However, it's a perfectly workable solution which may be what I'm left with. If it is, I won't be too unhappy.

[puppydogbuddy]

Hi Adrian,

see this link:

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

pDog

[Denburt]

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

[Code=vb]
Option Compare Database
Option Explicit
Dim GlobalFormVar As String
Private Sub Form_Current()
If Me.NewRecord = True Then
[/code]

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

[NeoPa]

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.Default Values is only one possible (and to my mind the most suitable) option.
Anyway, I've written some code.
...

Fish,

I'm not sure what you're surprised at here :confused:
However, try going through my latest post (#18) and see if there are any questions you still have unanswered. If so, I will attempt to answer clearly what you ask.

[NeoPa]

Hi Adrian,

see this link:

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

pDog

Nice link pDog.
It's not quite what I'm after (see Den's post after yours), but it certainly is interesting. The second method seemed a particularly neat way of triggering the switch too :)

[NeoPa]

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

[Code=vb]
Option Compare Database
Option Explicit
Dim GlobalFormVar As String
Private Sub Form_Current()
If Me.NewRecord = True Then
[/code]

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.