Limit subform to 1 entry

[TheSmileyCoder]

I have a subform "frm_Obs" on my main form "frm_Main"

If there is no records, I want the user to be able to add one. If there allready is one, I want the user to be able to change it, but not add anymore.

What I tried doing was add code to OnCurrent saying:

Private Sub Form_Current()
    Me.AllowAdditions = (Me.Recordset.RecordCount = 0)
End Sub

The problem is that when I go another record in the main form, and allowadditions is false, there will be no OnCurrent event fired, and thus the AllowAdditions will never be set back to False.

Suggestions people?

[ADezii]

Hello TheSmileyOne, I am literally running out the door, so I am simply throwing this out there for you to test. Execute the following code from the Current() Event of the Main Form, keeping in mind that you may possibly have to duplicate it in the Current() Event of the Sub-Form. I'm assuming, of course, that there are currently no more than 1 Record existing in the Sub-Form. I apologize for the rush:

Private Sub Form_Current()
With Me![frm_Obs].Form
  If .Recordset.RecordCount = 0 Then
    .AllowAdditions = True
  Else
    .AllowAdditions = False
    .AllowEdits = True
  End If
End With
End Sub

[nico5038]

Another option is to make sure the subform's record is inserted when the main form's record is. Thus no hassle afterwards with "Allowadditions " as every record will be there.
I often use a default in a field like "<Enter new data>" to show the user it's not filled yet....

Remains the question why you want to have a 1:1 relation which isn't "normalized "...

Nic;o)

[NeoPa]

I'm not sure it is a 1:1 as such Nico. It's a 1:?, where ? -> 0 or 1. There is therefore no expectation necessarily of having a record associated. Only that, should there be any, it never exceeds the single one.

[nico5038]

NeoPa,

1:0 or 1:1 can both be realized by adding the fields into the "master" table and filling (1:1) them or leave them Null (1:0) :-)

Nic;o)

[ADezii]

Hello Nico, shouldn't allowances be made in order to Add a Child Record at possibly a later date?

[nico5038]

Hi ADezii,

Could be, but it's not stated, thus my remark :-)

Nic;o)

[TheSmileyCoder]

The main form contains Tender Items to be evaluated. For each tender Item I have to perform 4 evaluations, 1 for each bidder. So the main item can have more then 1 child, it can júst only have 1 child for each bidder.

The table of evaluations however contain more then just these 4 evaluations per bidder per item.

Besides, just because I set up a 1-1 relation access would still show the newrecord possibility, it would jsut fail when I tried to save. To prevent that I want to set the AllowAdditions to False, when there allready is a record. The code provided by ADezii worked just fine. I don't know why I didn't consider that myself, I was so focused on doing it from within the subform itself.

[NeoPa]

Nico: 1:0 or 1:1 can both be realized by adding the fields into the "master" table and filling (1:1) them or leave them Null (1:0) :-)

Indeed. I hadn't quite realised you were suggesting a separate field in the master table.

I can however, conceive of situations where a designer may want to create a separate table, built on a 1:1 basis, to store such items as fit within a particular area. EG. In a db to manage a race it may be that charity sponsorship is involved for some, but not necessarily all, of the entrants. The designer may choose, quite sensibly, to have a separate 1:1 table to store this info without losing anything with regard to normalisation. The charity info may be quite voluminous, and may be considered a distraction in most uses of the entrant information. Only when it is required need it be included in the reporting.

[Dan C]

Thank You ADezii

Your fix above was just what I was looking for also. I needed a way to limit a single record in subform to show a prepayment, or advance, amount and check payment info. Your fix worked spectacularly. Now I just have to filter out the record to show only a check that was for a prepayment and not the first check for the purchase order.

Again thanks. A very useful solution.

[NeoPa]

Thanks for posting Dan. It's always nice when someone signs up just to express their appreciation :)

Welcome to Bytes!

[JeremyI]

Oldie but goodie! This thread saved me from pulling my hair out, too.

I can however, conceive of situations where a designer may want to create a separate table, built on a 1:1 basis, to store such items as fit within a particular area. EG. In a db to manage a race it may be that charity sponsorship is involved for some, but not necessarily all, of the entrants. The designer may choose, quite sensibly, to have a separate 1:1 table to store this info without losing anything with regard to normalisation. The charity info may be quite voluminous, and may be considered a distraction in most uses of the entrant information. Only when it is required need it be included in the reporting.

Or, in my case, the reason I found myself in this situation was that I'm working on a temporary (though lengthy) project largely relying on data compiled in an enormous, unwieldy spreadsheet. There are the core facts for employees, and then there are the project-specific details whose relevance will come and go. I thought those would best be placed in another table because Access may be fine up to 255 fields, but I'm not. 18 in one table and 21 in the other seems like quite enough.

Keep it up! :-)

[OldBirdman]

My thoughts on this thread.
I certainly agree with JeremyI about the number of fields in a record/file.
Although Access claims not to use more space than necessary for varying length strings, the same is not true for other fields. Access needs the space for numeric, boolean, and date fields whether used or not. If the fields of a child table are infrequently needed, then it saves space to not have all the fields in one table.
My logic says that queries on shorter records should be faster than queries on long records, as the number of bytes read would be less. Indexing of all fields used for query criteria would solve this, at some space & time expense to store & read the indexes.
Memo fields are also problematic. If only a few of these are changed frequently, and the length can vary from short to very long, my projects don't bloat as quickly if these fields are in another table.

[Sylvio]

Hello,

I've got a stupid thought on this topic. What if in a subform property sheet I go to "Format" and change "Navigation Buttons" property from Yes to No. This way I simply eliminate the possibility for user to go to next record in the subform and user can only work with one record. Please advise if this approach is acceptable.

[NeoPa]

There are more ways to navigate between records than simply to use the navigation buttons. As such, this may be an almost-solution, but not a reliable one i'm afraid.