By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Need help with form to display table with over 1000 records...

P: 19
I have a table called tblPayScales. It has 5 fields:

numRecID - record ID
txtCLASSCODE - a 1 or 2 letter text value (A, B, C, BD, L, LT, etc.)
numPAYSTEP - a number value 0-49
numSALARY - a number value such as 36,246.00
numNEWSALARY - a number value derived from a user-defined percentage

The table contains records for each CLASSCODE/PAYSTEP. Although there are 50 pay steps some have a value of 0 for numSALARY(no employee at that pay step). The NEWSALARY field is used to display calculated pay raises.

I need to display this information in a concise manner so that modifications can be made easily. I am currently stuck figuring out the best method to display the data. Only 1 class code needs to be displayed at a time but I need the results to display in a single form window. I say form because I think a drop-down is needed to choose the class code. I also need a command button on the form to perform a calculation on the salary field to update the numNEWSALARY field. I'm pretty confident I can get the percentage using an inputbox routine but that will need to be passed to an update query.

All suggestions are welcomed.

TIA,

William
Apr 27 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,616
I have a table called tblPayScales. It has 5 fields:

numRecID - record ID
txtCLASSCODE - a 1 or 2 letter text value (A, B, C, BD, L, LT, etc.)
numPAYSTEP - a number value 0-49
numSALARY - a number value such as 36,246.00
numNEWSALARY - a number value derived from a user-defined percentage

The table contains records for each CLASSCODE/PAYSTEP. Although there are 50 pay steps some have a value of 0 for numSALARY(no employee at that pay step). The NEWSALARY field is used to display calculated pay raises.

I need to display this information in a concise manner so that modifications can be made easily. I am currently stuck figuring out the best method to display the data. Only 1 class code needs to be displayed at a time but I need the results to display in a single form window. I say form because I think a drop-down is needed to choose the class code. I also need a command button on the form to perform a calculation on the salary field to update the numNEWSALARY field. I'm pretty confident I can get the percentage using an inputbox routine but that will need to be passed to an update query.

All suggestions are welcomed.

TIA,

William
An Unbound Sub-Form on a Parent Form would enable you to achieve your goal. This would keep all your data confined to a single Form window, but be flexible enough to display a large number of Records.
Apr 28 '07 #2

P: 19
An Unbound Sub-Form on a Parent Form would enable you to achieve your goal. This would keep all your data confined to a single Form window, but be flexible enough to display a large number of Records.
I was thinking along the same lines but can't figure out the best way to display the results in the subform. With 50 records per CLASSCODE the only way I can see to do it is to have a sub-form and format the results to look something like this:

Step-------------------------Salary-------------------------Step-------------------------Salary
0------------------------------$10,000.00-------------------4-----------------------------$12,000.00
1------------------------------$10,500.00-------------------5-----------------------------$12,500.00
2------------------------------$11,000.00-------------------6-----------------------------$13,000.00
3------------------------------$11,500.00-------------------7-----------------------------$13,500.00
etc....

A drop-down list in the main form will accomplish the task of choosing the classification code. Any tips on formatting the sub-form?
Apr 28 '07 #3

ADezii
Expert 5K+
P: 8,616
I was thinking along the same lines but can't figure out the best way to display the results in the subform. With 50 records per CLASSCODE the only way I can see to do it is to have a sub-form and format the results to look something like this:

Step-------------------------Salary-------------------------Step-------------------------Salary
0------------------------------$10,000.00-------------------4-----------------------------$12,000.00
1------------------------------$10,500.00-------------------5-----------------------------$12,500.00
2------------------------------$11,000.00-------------------6-----------------------------$13,000.00
3------------------------------$11,500.00-------------------7-----------------------------$13,500.00
etc....

A drop-down list in the main form will accomplish the task of choosing the classification code. Any tips on formatting the sub-form?
Wouldn't a Sub-Form in Datasheet View solve your problem? I'm not sure what the extent of your Horizontal Scrolling would be.
Apr 28 '07 #4

P: 19
Thanks. The datasheet view worked with a bit of formatting. Now I could use some help updating the sub-form after the user chooses the class code from a drop-down list.

I have created a query (qryViewScale) that will filter according to the Class code chosen(I run the query and type in the letter(s) of the class code). I have also created a table called tblSCALENAMES to hold the short and long names for the classification codes. (ex. A, Administrative, BD, Bus Driver, CK, Cook, etc). I've created a combo box on the main form which uses [tblSCALENAMES]![txtCLASSCODE] to list all classification codes (good so far). I then created a button on the main form called cmdUpdateView which, when clicked, needs to update the view in the sub-form. It should display only the records from the classification chosen in the combo box. Here's where I got stuck again. I can't figure out what to put in the on-click event procedure for the button. Any help or an pointer to an example would be appreciated.
Apr 30 '07 #5

ADezii
Expert 5K+
P: 8,616
Thanks. The datasheet view worked with a bit of formatting. Now I could use some help updating the sub-form after the user chooses the class code from a drop-down list.

I have created a query (qryViewScale) that will filter according to the Class code chosen(I run the query and type in the letter(s) of the class code). I have also created a table called tblSCALENAMES to hold the short and long names for the classification codes. (ex. A, Administrative, BD, Bus Driver, CK, Cook, etc). I've created a combo box on the main form which uses [tblSCALENAMES]![txtCLASSCODE] to list all classification codes (good so far). I then created a button on the main form called cmdUpdateView which, when clicked, needs to update the view in the sub-form. It should display only the records from the classification chosen in the combo box. Here's where I got stuck again. I can't figure out what to put in the on-click event procedure for the button. Any help or an pointer to an example would be appreciated.
Here is the basic Code Template. Basically, you are modifying the RecordSource of the Sub-Form depending on what Class Code is selected in the Class Code Combo Box (cboClassCode):
Expand|Select|Wrap|Line Numbers
  1. Private cmdUpdateView_AfterUpdate()
  2. 'Private Variable to hold the selected Class Code
  3. Dim strClassCode As String
  4.  
  5. strClassCode = Me![cboClassCode]
  6.  
  7. If IsNull(strClassCode) Then
  8.   Exit Sub
  9. Else
  10.   Me![<your sub-form control name>].Form.RecordSource = _
  11.   "Select * From tblPayScales Where [ClassCode] = '" & strClassCode & "'"
  12. End If
  13. End Sub
NOTE: Substitute your Field Name(s), Table Name(s) wherever appropriate.
Apr 30 '07 #6

P: 19
Created the code as you suggested making changes to the names.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdateScale_Click()
  2. 'Private Variable to hold the selected Class Code
  3. Dim strClassCode As String
  4.  
  5. strClassCode = Me![cboSelectCode]
  6.  
  7. If IsNull(strClassCode) Then
  8. Exit Sub
  9. Else
  10. Me![subViewPayScale].Form.RecordSource = _
  11. "Select * From tblPayScales Where [txtCLASSCODE] = '" & strClassCode & "'"
  12. End If
  13. End Sub
If I select the first classification code (A) it filters the sub-form. If I choose any other code using the combo-box it returns no results (sub form updates with no data in fields). If I use the record navigation at the bottom of the window it will show the correct scale in the window. Example:
I choose class code A (record 1 in the table) in the combo box and click the view button it filters correctly.

If I change the class code in the combo box to B (record 2) the form shows no results. If I use the record navigation to change to record 2, then the results for B show back up in the window. If I change the combo box back to A and click the button there are no results in the window until I match the record navigation again.
Apr 30 '07 #7

ADezii
Expert 5K+
P: 8,616
Created the code as you suggested making changes to the names.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdateScale_Click()
  2. 'Private Variable to hold the selected Class Code
  3. Dim strClassCode As String
  4.  
  5. strClassCode = Me![cboSelectCode]
  6.  
  7. If IsNull(strClassCode) Then
  8. Exit Sub
  9. Else
  10. Me![subViewPayScale].Form.RecordSource = _
  11. "Select * From tblPayScales Where [txtCLASSCODE] = '" & strClassCode & "'"
  12. End If
  13. End Sub
If I select the first classification code (A) it filters the sub-form. If I choose any other code using the combo-box it returns no results (sub form updates with no data in fields). If I use the record navigation at the bottom of the window it will show the correct scale in the window. Example:
I choose class code A (record 1 in the table) in the combo box and click the view button it filters correctly.

If I change the class code in the combo box to B (record 2) the form shows no results. If I use the record navigation to change to record 2, then the results for B show back up in the window. If I change the combo box back to A and click the button there are no results in the window until I match the record navigation again.
I am currently under the assumption that the only Control on the Main Form is cboClassCode and that all the data exists in the Sub-Form based on tblPayScales. Wasn't this the design concept? To display the maximum amount of data (Records) for a given Class Code? I was also under the impression that the Main Form was Unbound, the only purpose of it being to filter for various Class Codes? If my impression is wrong I apologize - please fill me in on what I am missing and I'll be glad to further assist you.
Apr 30 '07 #8

P: 19
I am currently under the assumption that the only Control on the Main Form is cboClassCode and that all the data exists in the Sub-Form based on tblPayScales. Wasn't this the design concept? To display the maximum amount of data (Records) for a given Class Code? I was also under the impression that the Main Form was Unbound, the only purpose of it being to filter for various Class Codes? If my impression is wrong I apologize - please fill me in on what I am missing and I'll be glad to further assist you.
I'm sorry for the confusion. Just to make sure I'm being clear I'll re-iterate the current layout of the various tables and forms. Please forgive my ignorance. :)

tblScaleNames
The main form is frmViewPayScales and is bound to the table tblScaleNames. The sub-form is called subViewPayScales and is bound to the table tblPayScales. frmViewPayScales contains two controls: a combobox called cboClassCode and a command button called cmdUpdateView.

cboClassCode has the following statement assigned to the Row Source:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblScaleNames.txtCLASSCODE, tblScaleNames.txtCLASSDESC FROM tblScaleNames ORDER BY [txtCLASSCODE]; 
It will properly display a list of classification codes and descriptions.

cmdUpdateView is the command button to update subViewPayScales and has the following code in the Click event procedure:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdateView_Click()
  2. 'Private Variable to hold the selected Class Code
  3. Dim strClassCode As String
  4.  
  5. strClassCode = Me![cboClassCode]
  6.  
  7. If IsNull(strClassCode) Then
  8.   Exit Sub
  9. Else
  10.   Me![subViewPayScale].Form.RecordSource = _
  11.   "Select * From tblPayScales Where [tblPayScales]![txtCLASSCODE] = '" & strClassCode & "'"
  12. End If
  13. End Sub
May 1 '07 #9

P: 19
Update:

I mistakenly bound the main form to the table when it should have been unbound. I also linked the sub-form to the table, not to the query, which caused the weird issue I was previously describing. It's working as expected now.

Thanks ADezii so much for your patience and guidance! I've really learned a lot so far... :)
May 1 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.