Limit rows in subform based on field in main form

I have a main form that has a number field (this field inidcates the number 
of seats available). I want the subform to only allow the user to enter data 
in rows that are equal or less than than the number field in the main form. 

please help with code and best place to enter.

many thanks!!!
0
Utf
3/2/2010 11:50:01 PM
access.forms 6864 articles. 2 followers. Follow

5 Replies
1849 Views

Similar Articles

[PageSpeed] 16

cynteeuh wrote:
>I have a main form that has a number field (this field inidcates the number 
>of seats available). I want the subform to only allow the user to enter data 
>in rows that are equal or less than than the number field in the main form. 
>
>please help with code and best place to enter.
>
>many thanks!!!

You need to use a RecordsetClone to get the count of records in the subform.
Then in the BeforeInsert event, check that 

If RecordsetClone.RecordCount<MAX_CHILD_RECORDS Then
      'MsgBox...
      Cancel = True
End If

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201003/1

0
PieterLinden
3/3/2010 2:09:55 AM
On Tue, 2 Mar 2010 15:50:01 -0800, cynteeuh
<cynteeuh@discussions.microsoft.com> wrote:

In some subforms users can sort the data differently, so just making
the top N rows editable is not going to be good enough. How will we
know which rows you mean?

In general terms you can write code in the subform's Form_Current
event and set Me.AllowEdits to True or False based on if it's in the
first N rows or not.
Me.RecordsetClone.AbsolutePosition can be used to get the 0-based row
count.

-Tom.
Microsoft Access MVP


>I have a main form that has a number field (this field inidcates the number 
>of seats available). I want the subform to only allow the user to enter data 
>in rows that are equal or less than than the number field in the main form. 
>
>please help with code and best place to enter.
>
>many thanks!!!
0
Tom
3/3/2010 2:16:38 AM
On Wed, 03 Mar 2010 02:09:55 GMT, "PieterLinden via AccessMonster.com"
<u49887@uwe> wrote:

Interesting. I took his question to mean: I may have more than N
records, and the user can only enter data in the first N ones.
You took it to mean: I want to prevent the user from entering more
than N rows.

-Tom.
Microsoft Access MVP


>cynteeuh wrote:
>>I have a main form that has a number field (this field inidcates the number 
>>of seats available). I want the subform to only allow the user to enter data 
>>in rows that are equal or less than than the number field in the main form. 
>>
>>please help with code and best place to enter.
>>
>>many thanks!!!
>
>You need to use a RecordsetClone to get the count of records in the subform.
>Then in the BeforeInsert event, check that 
>
>If RecordsetClone.RecordCount<MAX_CHILD_RECORDS Then
>      'MsgBox...
>      Cancel = True
>End If
0
Tom
3/3/2010 2:18:40 AM
Tom van Stiphout wrote:
>Interesting. I took his question to mean: I may have more than N
>records, and the user can only enter data in the first N ones.
>You took it to mean: I want to prevent the user from entering more
>than N rows.
>
>-Tom.
>Microsoft Access MVP
>

That's what I thought the poster meant... only because nothing else made
sense.  Maybe I'm wrong...

-- 
Message posted via http://www.accessmonster.com

0
PieterLinden
3/3/2010 4:55:37 AM
Pieter is correct in what I meant. Sorry for any confusion. I'll definitely 
give this a try.

Thank you!!!

"PieterLinden via AccessMonster.com" wrote:

> Tom van Stiphout wrote:
> >Interesting. I took his question to mean: I may have more than N
> >records, and the user can only enter data in the first N ones.
> >You took it to mean: I want to prevent the user from entering more
> >than N rows.
> >
> >-Tom.
> >Microsoft Access MVP
> >
> 
> That's what I thought the poster meant... only because nothing else made
> sense.  Maybe I'm wrong...
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
5/25/2010 7:13:01 PM
Reply:

Similar Artilces:

Edit or Create a Partylist Lookup field
I have an issue - we have Cytrack running between our AVAYA phones and CRM 4.0 and I have enable the option that when a call comes through it opens a Phone Call activity and it automatically connects to the contact for the person calling in. Great except while we have all cleitns in as contacts - we also have clients either in as a Lead or an Opportunity when we are doign current business and I want to change the phone call activity recipient. I can do this but it comes up a "Look Up Records" screen and you havea drop down list to pick what entity you wish to locate the cl...

loop through pivottables columns and rows
I have a pivot table that have 2 columns that I need to loop through and read both columns and each items of each columns...the pivotitems only allow reading one column...does anyone know how to programmatically read both columns? Thanks You can loop through the column fields and their items: Sub test() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.ColumnFields For Each pi In pf.PivotItems Debug.Print pf.Name & " - " & pi.Name Next pi Next pf End Sub hpham77 wrote: > I have a pivot ta...

Pivottable Row SubTotals
I have just started using pivot tables, and have created one with sub totals at the end of each row. Is there a way of getting the totals to be at the begining of each row? Thanks in advance. That isn't an option provided by pivot tables. -- Regards, Tom Ogilvy Matthew <matthew@coxagri.com> wrote in message news:a69014c2.0310091309.59241b0f@posting.google.com... > I have just started using pivot tables, and have created one with sub > totals at the end of each row. > > Is there a way of getting the totals to be at the begining of each > row? > > Thanks in ad...

Online form ~ I have word 2003 and 2007
Hello, I am trying to create an online form that will allow for a limited amount of characters per field. For example: Name: 20 Characters at length and so on Date: 15 Characters Title of the reading: 40 characters Please advise, Thanks -- astro44 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader an...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

Inserting Rows Problem
Hi there, I have a list of about 2000 rows. However, in between each of thes rows I want to insert a blank row that can be used for people to writ in once the list is printed off. Is there a quick way to do this? Kind regards Andre -- andyp16 ----------------------------------------------------------------------- andyp161's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1165 View this thread: http://www.excelforum.com/showthread.php?threadid=26611 If you insert rows it will affect sorting, etc. and be a real pain. Many posts here want to delete blank rows ...

Purge data based on another Excel file???
I am attempting to purge data from a large list based on data in another document. I have the files I want to remaove in on file and the file I want to remove them from. There should be simple way to do this no? ...

Preventing writing in a combo box field
Ok this is my problem: I've a combo box with several employee names. Of course that when the employee is chosing it's name it writes the first letter so that the combo box filters the names to the closest one. Access then higlightes the employee name execpt the letter he wrote. Employee then hits enter and Access displays its full name, has in the combo box options, neverthless I have a hidden form that copies employee name to feed other forms. The problem is that if employee chooses is name has discribed, Access will only select all the other letters of his name execpt the one ...

Duplicating reocords from main form and subform receiving Runtime Error 3022
I am trying to develop a command button that will duplicate the main form and the subform information and save it to another record within the same tables. I used Allen Browne's program and modified it to fit my tables. At first it would copy the main form and not the subform data so I made some changes and now I am getting the Runtime error 3022. I have worked on this off and on for about 2 weeks. Any help on getting this to run will be greatly appreciated. Below is my code: Private Sub Command78_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main ...

Copying text value based on long (comlex) calculation
I am not sure how to even search for what I want to do, so please forgive me if my first post has been discussed and resolved ad naseum in the past... Here is my situation: - I have a workbook that contains multiple sheets. - Each sheet contains auto parts from different suppliers, sorted by part number. - Some suppliers have parts that overlap with each other and the various sheets are updated as pricing and availabilty of parts change. - I am using a combination of the VLOOKUP and MIN functions to determine which supplier has lowest cost based on a comparison of part numbers and price ac...

selecting rows of data
I have an excel worksheet with 4 columbs. I want to automatically select multiple rows of data according to search which will partly match one cell in the row and put the full ro as results in a new worksheet. for example 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete 22/03/03,Texas,90.78,incomplete 23/04/03,Oregon,467.99,complete If the search string is "New" how can I produce a new worksheet whic will contain 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete Any suggestions would be appriciated -- Message posted from http://w...

Total from a Query to a Form
Hello, I have several queries that have count totals from certain fields in a table. I would like to be able to insert those totals (just the numbers) on certain places in another form for a manager's summary view. How can I make this possible. Thanks in advance. On Sep 25, 8:34 am, Chris <chris.ho...@gmail.com> wrote: > Hello, > > I have several queries that have count totals from certain fields in a > table. I would like to be able to insert those totals (just the > numbers) on certain places in another form for a manager's summary > view. How can I ma...

Delete duplicate rows from a list in Excel
Hi, Below are instructions on how to "delete duplicate rows from a list in Excel". I learned about this tip from the Microsoft Office Assistant website. However, each time I try step 4, my list is not filtered and no records are hidden. Is there a secret I am missing to make this work? Thanks for your help! -Greg http://office.microsoft.com/en-us/assistance/HA010346261033.aspx Applies to Microsoft Office Excel 2003 Microsoft Excel 2002 A duplicate row (also called a record) in a list is one where all values in the row are an exact match of all the values in another row. To delet...

Nested query-based distribution groups?
We are currently trying to implement some query-based distribution groups. Is it possible to create one QBDG that has a couple of rules (for example all people with a certain job title) and then create another dynamic group that will start with all the users in the previous group (all people with a certain title) and further narrow it down with more rules? Basically does exchange 2003 allow for nested query-based groups? If so, how do you go about doing it? Thanks. Yes they can be nested. You would nest them just like you would nest any other group. http://support.microsoft.com/?id=82289...

rows and columns in spreadsheet
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Is there a way, in Excel, that I can merge cells, rows, or columns, like I can in Numbers? Many thanks for your help. ...

Linking 2 Subforms
After reading several posts on these forums, I am beginning to think that what I would like to do is impossible. I don't know if anyone can point me in the right direction or not, but here goes. I currently have a form with 2 subforms (both in datasheet view). The main form has combo boxes that filter the results in the first subform. I would like to be able to select a record in the first subform and have on the related records shown in the second subform. I believe this has something to do with Parent-Child links, but i know that you cannot have a parent-child link between 2 ...

removing locked cost field from product form
On the Product entity form the 3 fields 'list price' 'standard cost' and 'current cost' are locked. Is there a way to get the 'current cost' field off the form so it can't be seen by users? thanks Garnet Hi Garnet, You can use the following Javascript Onload of the form: crmForm.all.<<name of the field>>_d.style.display = "none"; HTH, Niths "Garnet" wrote: > On the Product entity form the 3 fields 'list price' 'standard cost' and > 'current cost' are locked. Is there a way to get the &#...

Outlook Form "Array Index out of bounds"
I recently installed MSOffice 2003 witjh Small Business Manager. After deleting an attached e-mail I am no longer able to launch the custom form and receive an error that says "Array Index out of bounds". I know it has to do with launching a form but I cannot figure out how to "fix" this. Any help would be greatly appreciated. Thanks in advance.... ...

Summation of Rows Problem
I have two worksheets and have created a formula to sum cells ie sum(G4:G200)from worksheet A in worksheet B. However when I delete or add rows from worksheet A the formula changes accordingly. Is there a way to lock the formula so that it remains as sum(G4:G200) even if rows with the range are added or deleted in worksheet A. Thanks Couple of ways: =SUM(INDIRECT("Sheet1!G4:G200")) And, non-volatile: =SUM(INDEX(Sheet1!G:G,ROW(4:4)):INDEX(Sheet1!G:G,ROW(200:200))) -- HTH, RD ===================================================== Please keep all correspondence within the Gro...

form design issues
Hello Access 2007 here, 2 quick questions: 1) how can I manage comboboxes do NOT change background on exit? It seems that by default, once you exit the control it kind of make its background transparent, even if either backcolor or backstyle values didn't change, I ckecked. 2) I'm noticing that after starting typing in an unbound form, the labels text seems kind of bolder, not looking "regular" as when the form opened. I read some years ago something about issues regarding memory licking that can cause weird *things*. Does anyone know something more accurate? ...

Text field size not matching
The help file says that custom fields text1-30 can have up to 255 characters but when I go to add that field to my Task Sheet it will only allow 128 characters. Any info would be greatly appreciated. Hi madsmom, I can add/view approximately 251 characters in the field when added to the table. Did you press enter when typing? If so, it will truncate at the first return. I hope this helps. Let us know how you get along. Julie Project MVP Visit http://project.mvps.org/ for the FAQs and additional information about Microsoft Project "madsmom" <madsmom@dis...

Format Cells dialog box displays 2 rows of tabs
I have Windows XP with O2K and XP installed to "play" together. However, when opening the Format Cells dialog box, it displays two rows of tabs rather than a single row. Any help to get this changed would be much appreciated! ...

Search field no longer working
I recently converted an Access file from 97 to Office 2000. There were search fields that worked prior to the conversion. Now, when I try to search the database I get a "The control 'Find Record' the macro is attempting to search cannot be searched. If I open the database in Office 97 the search capability works fine. I did not write this program nor am I a guru in Access. Though vauge can anyone enlighten a newbie as to what may be happening? Thanks. ...

Insert blank rows repeatedly between every data row in Excel
Could you guide me please.... I need to insert 5 blank rows repeatedly between every existing data rows for approximately 300 rows. If I go about doing the repeat short-cut "Control+Y", it just repeats inserting ONE row only between the consecutive data row. Is there some command, which helps me highlight all the rows & allows me to insert 5 blank rows between every consecutive existing data row? Thanks in advance for your kind advice. Hi Being very new to excel coding, this code might be lengthy, but it works. Public Sub insert_row() Const TestColumn As Long = 1 '...

Deleting rows containing zeros
I am importing a variable amount (10,000+) rows of source data into an Excel 2002 worksheet. Many of the rows will contain zero in column B (total quantity) or zero in column C (total cost) which are not required. How can I automatically delete rows that contain zeros in BOTH columns? The intention is then to rank the remaining data. Thanks in anticipation. There is no automatic way to delete rows. In the end that will be a process of steps of some sort. If you are getting 10,000 + rows of source data then I assume that it is coming from a data base of some sort. How about modi...