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

[wparrott]

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

[ADezii]

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.

[wparrott]

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?

[ADezii]

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.

[wparrott]

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.

[ADezii]

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

Private cmdUpdateView_AfterUpdate()
'Private Variable to hold the selected Class Code
Dim strClassCode As String
 
strClassCode = Me![cboClassCode]
 
If IsNull(strClassCode) Then
  Exit Sub
Else
  Me![<your sub-form control name>].Form.RecordSource = _
  "Select * From tblPayScales Where [ClassCode] = '" & strClassCode & "'"
End If
End Sub

NOTE: Substitute your Field Name(s), Table Name(s) wherever appropriate.

[wparrott]

Created the code as you suggested making changes to the names.

Private Sub cmdUpdateScale_Click()
'Private Variable to hold the selected Class Code
Dim strClassCode As String
 
strClassCode = Me![cboSelectCode]
 
If IsNull(strClassCode) Then
Exit Sub
Else
Me![subViewPayScale].Form.RecordSource = _
"Select * From tblPayScales Where [txtCLASSCODE] = '" & strClassCode & "'"
End If
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.

[ADezii]

Created the code as you suggested making changes to the names.

Private Sub cmdUpdateScale_Click()
'Private Variable to hold the selected Class Code
Dim strClassCode As String
 
strClassCode = Me![cboSelectCode]
 
If IsNull(strClassCode) Then
Exit Sub
Else
Me![subViewPayScale].Form.RecordSource = _
"Select * From tblPayScales Where [txtCLASSCODE] = '" & strClassCode & "'"
End If
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.

[wparrott]

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:

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:

Private Sub cmdUpdateView_Click()
'Private Variable to hold the selected Class Code
Dim strClassCode As String
 
strClassCode = Me![cboClassCode]
 
If IsNull(strClassCode) Then
  Exit Sub
Else
  Me![subViewPayScale].Form.RecordSource = _
  "Select * From tblPayScales Where [tblPayScales]![txtCLASSCODE] = '" & strClassCode & "'"
End If
End Sub

[wparrott]

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