C
Chuck
Marshall,
Great work ! ! !
Chuck
--
Great work ! ! !
Chuck
--
Tom said:Believe you are correct. Think you have a winner here. Let me do some
more testing, but I think you have done it! The dashed and dotted numbers
are sorting perfectly!!
Tom said:Have tested the code. All seems well at this point.
I did run into a part number the uses a front slash / in the part to
indicate fractions that caused Access to object, but decided I could do
without the numbers and deleted them.
Appreciate your netiquette comments. I was mindful at the time, but
thought a complete look for you would be valuable.
Out of curiosity, what city do you live in? I travel sometimes and would
like to buy you dinner or drinks or something
Tom said:I get into the northern Indiana, southern Illinois area frequently. I have
a customer in the Broadview, IL area and call on accounts closer to
downtown Chicago as well. Is any of these areas close?
Tom said:The code is working perfectly. In the Reports I want the sort, I placed
=StandardizePartNum([Comp Part]) in the Sorting & Grouping area. This
sorts the field Comp Part (the competitor's part) as indicated.
The field for my company's part is RayPart. For my other Brands the
database supports, my part could be Federated, Aimco or Spicer. What if I
wanted to give the user a choice of which field to sort at Report run time?
Could the Comp Part be replaced by a user selection using a Check Box or
Drop Down List that places the choice in a Table that remembers the choice?
I am currently using Check Boxes to accommodate user selections for other
possible options in the database. Here is some of the code in use:
This code checks the user's last selection and restores it in the Check
Box:
Private Sub form_open(Cancel As Integer)
Dim db As Database
Dim rsRay As Recordset
Dim rsSilver As Recordset
Dim rsAimco As Recordset
Dim rsSpicer As Recordset
Dim intRaySetting As Integer
Dim intSilverSetting As Integer
Dim intAimcoSetting As Integer
Dim intSpicerSetting As Integer
Set db = CurrentDb
Set rsRay = db.OpenRecordset("Raymold Option Status")
rsRay.MoveFirst
intRaySetting = rsRay!Setting
RaymoldOpt.Value = intRaySetting
rsRay.Close
'Added Code
Set rsSilver = db.OpenRecordset("Silver Option Status")
rsSilver.MoveFirst
intSilverSetting = rsSilver!Setting
SilverOpt.Value = intSilverSetting
rsSilver.Close
'Added Code
Set rsAimco = db.OpenRecordset("Aimco Option Status")
rsAimco.MoveFirst
intAimcoSetting = rsAimco!Setting
AimcoOpt.Value = intAimcoSetting
rsAimco.Close
'Added Code
Set rsSpicer = db.OpenRecordset("Spicer Option Status")
rsSpicer.MoveFirst
intSpicerSetting = rsSpicer!Setting
SpicerOpt.Value = intSpicerSetting
rsSpicer.Close
End Sub
This code is how the user selects a particular option and stores the choice
as a 1 or a 2 in the applicable Option Status Table:
Private Sub optRayOff_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "1"
rs.Update
rs.Close
RaymoldOpt.Value = "1"
End Sub
Private Sub optRayOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
RaymoldOpt.Value = "2"
End Sub
Is there a way for the user to select and store one of five possible sort
columns and pass the choice to this line =StandardizePartNum([Comp Part])?
Tom said:The code is working perfectly. In the Reports I want the sort, I placed
=StandardizePartNum([Comp Part]) in the Sorting & Grouping area. This
sorts the field Comp Part (the competitor's part) as indicated.
The field for my company's part is RayPart. For my other Brands the
database supports, my part could be Federated, Aimco or Spicer. What if I
wanted to give the user a choice of which field to sort at Report run time?
Could the Comp Part be replaced by a user selection using a Check Box or
Drop Down List that places the choice in a Table that remembers the choice?
I am currently using Check Boxes to accommodate user selections for other
possible options in the database. Here is some of the code in use:
This code checks the user's last selection and restores it in the Check
Box:
Private Sub form_open(Cancel As Integer)
Dim db As Database
Dim rsRay As Recordset
Dim rsSilver As Recordset
Dim rsAimco As Recordset
Dim rsSpicer As Recordset
Dim intRaySetting As Integer
Dim intSilverSetting As Integer
Dim intAimcoSetting As Integer
Dim intSpicerSetting As Integer
Set db = CurrentDb
Set rsRay = db.OpenRecordset("Raymold Option Status")
rsRay.MoveFirst
intRaySetting = rsRay!Setting
RaymoldOpt.Value = intRaySetting
rsRay.Close
'Added Code
Set rsSilver = db.OpenRecordset("Silver Option Status")
rsSilver.MoveFirst
intSilverSetting = rsSilver!Setting
SilverOpt.Value = intSilverSetting
rsSilver.Close
'Added Code
Set rsAimco = db.OpenRecordset("Aimco Option Status")
rsAimco.MoveFirst
intAimcoSetting = rsAimco!Setting
AimcoOpt.Value = intAimcoSetting
rsAimco.Close
'Added Code
Set rsSpicer = db.OpenRecordset("Spicer Option Status")
rsSpicer.MoveFirst
intSpicerSetting = rsSpicer!Setting
SpicerOpt.Value = intSpicerSetting
rsSpicer.Close
End Sub
This code is how the user selects a particular option and stores the choice
as a 1 or a 2 in the applicable Option Status Table:
Private Sub optRayOff_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "1"
rs.Update
rs.Close
RaymoldOpt.Value = "1"
End Sub
Private Sub optRayOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
RaymoldOpt.Value = "2"
End Sub
Is there a way for the user to select and store one of five possible sort
columns and pass the choice to this line =StandardizePartNum([Comp Part])?
Whoa, you should explain what all those recordsets are for
and why it looks like you have separate tables for each
choice. It looks like there is only one record (and one
field?) in those tables. If that's a fair assumption, then
didn't you combine all that into one table, probably with
just a single value for the selected brand.
I also don't understand what the GotFocus event has to do
with much of anything. Normally, the check box's
AfterUpdate event would be more appropriate for saving the
setting. Why you have two check boxes for optRayOff and
optRayOn is beyond me. I would think that you would have a
cleaner UI and less code if you used either a combo box or
an option group.
After you get all that sorted out, you can use the report's
Open event to set the Sorting and grouping expression.
Assuming you have one table with the selected brand/field,
the code in the report's Open event procedure would be
something along these lines:
Me.GroupLevel(0) = "=StandardizePartNum([" & _
& DLookup("Setting", "Settings") & "])"
That probably won't do any more for you than give you a
vague idea of how to set a report's sorting when the report
is opened, but I am really lost as far as how you have
designed your tables.
Tom said:Tom said:The code is working perfectly. In the Reports I want the sort, I placed
=StandardizePartNum([Comp Part]) in the Sorting & Grouping area. This
sorts the field Comp Part (the competitor's part) as indicated.
The field for my company's part is RayPart. For my other Brands the
database supports, my part could be Federated, Aimco or Spicer. What if I
wanted to give the user a choice of which field to sort at Report run time?
Could the Comp Part be replaced by a user selection using a Check Box or
Drop Down List that places the choice in a Table that remembers the choice?
I am currently using Check Boxes to accommodate user selections for other
possible options in the database. Here is some of the code in use:
This code checks the user's last selection and restores it in the Check
Box:
Private Sub form_open(Cancel As Integer)
Dim db As Database
Dim rsRay As Recordset
Dim rsSilver As Recordset
Dim rsAimco As Recordset
Dim rsSpicer As Recordset
Dim intRaySetting As Integer
Dim intSilverSetting As Integer
Dim intAimcoSetting As Integer
Dim intSpicerSetting As Integer
Set db = CurrentDb
Set rsRay = db.OpenRecordset("Raymold Option Status")
rsRay.MoveFirst
intRaySetting = rsRay!Setting
RaymoldOpt.Value = intRaySetting
rsRay.Close
'Added Code
Set rsSilver = db.OpenRecordset("Silver Option Status")
rsSilver.MoveFirst
intSilverSetting = rsSilver!Setting
SilverOpt.Value = intSilverSetting
rsSilver.Close
'Added Code
Set rsAimco = db.OpenRecordset("Aimco Option Status")
rsAimco.MoveFirst
intAimcoSetting = rsAimco!Setting
AimcoOpt.Value = intAimcoSetting
rsAimco.Close
'Added Code
Set rsSpicer = db.OpenRecordset("Spicer Option Status")
rsSpicer.MoveFirst
intSpicerSetting = rsSpicer!Setting
SpicerOpt.Value = intSpicerSetting
rsSpicer.Close
End Sub
This code is how the user selects a particular option and stores the choice
as a 1 or a 2 in the applicable Option Status Table:
Private Sub optRayOff_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "1"
rs.Update
rs.Close
RaymoldOpt.Value = "1"
End Sub
Private Sub optRayOn_GotFocus()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Raymold Option Status")
rs.MoveFirst
rs.Edit
rs!Setting = "2"
rs.Update
rs.Close
RaymoldOpt.Value = "2"
End Sub
Is there a way for the user to select and store one of five possible sort
columns and pass the choice to this line =StandardizePartNum([Comp Part])?
Whoa, you should explain what all those recordsets are for
and why it looks like you have separate tables for each
choice. It looks like there is only one record (and one
field?) in those tables. If that's a fair assumption, then
didn't you combine all that into one table, probably with
just a single value for the selected brand.
I also don't understand what the GotFocus event has to do
with much of anything. Normally, the check box's
AfterUpdate event would be more appropriate for saving the
setting. Why you have two check boxes for optRayOff and
optRayOn is beyond me. I would think that you would have a
cleaner UI and less code if you used either a combo box or
an option group.
After you get all that sorted out, you can use the report's
Open event to set the Sorting and grouping expression.
Assuming you have one table with the selected brand/field,
the code in the report's Open event procedure would be
something along these lines:
Me.GroupLevel(0) = "=StandardizePartNum([" & _
& DLookup("Setting", "Settings") & "])"
That probably won't do any more for you than give you a
vague idea of how to set a report's sorting when the report
is opened, but I am really lost as far as how you have
designed your tables.
The Option Status Tables are used to store the status of a user selection.
The selection is designed to stay or stick or be remembered in the same
session and restore the last setting when a new session is started, thus
the four Tables. Each Table has one field named Setting and one record
with a number 1 or 2 stored in it. The Option is either ON (2) or OFF (1).
When the Option is toggled, there is some code run (that I took out of my
sample) that manipulates the field names in the master interchange Tables
along with changing the Option Selection and storing the result in the
Option Table.
The Check Boxes for On and Off are on a Form as part of an Option Group.
When the Form is opened, the Form_Open Code checks the last status of the
four option boxes by looking in the Option Status Tables and restores the
Check Mark in the appropriate Check Box. When the user selects the
alternate box in the Option Group, that is what triggers the Got Focus
attribute for the Check Box and runs the attached code.
I hope I didn't confuse you. I only offered those bits of code as
something I am already using for a user selection.
OK, if I had these Descriptive Names and Field choices for Report sorting:
Competitive Part - Comp Part
Raybestos Part - RayPart
Federated Part - Federated
Spicer Chassis Part - Spicer
Aimco Part - Aimco
What would you recommend for a UI to select one of the choices and store
the result in the Table Setting for use in the above Me String you posted
above? Maybe a Drop Down Box showing the Five Descriptive Names on the
left that are tied to the 5 Field names on the left and those Field Names
are stored in the Table that the Me. code looks for during its DLookup?
Me.GroupLevel(0) = "=StandardizePartNum([" & _
& DLookup("Setting", "Settings") & "])"
Tom said:Marsh,
When I run this code from the On Open Event Procedure,
Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub
I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.
Tom said:Marsh,
When I run this code from the On Open Event Procedure,
Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub
I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.
You must precreate an entry in the Sorting and Grouping
window. I thought you said you had already done that??
Once you get the report sorting as you said it was, then add
the code to change it to the use the field from the settings
table. (Double check to make sure the record in the
Settings table has the name of the field you want to use.)
I did have this line =StandardizePartNum([Comp Part]) in the Sorting and
Grouping Window and it is working perfectly, but removed it thinking it was
redundant with the new code string you sent. It is now back and I have
tried to modify it per your example.
I have a Table Settings with a single Field named Setting. It has one row
with the entry Comp Part.
OK, based on your second paragraph, I don't need anything in the On Open
event. All I need to do is mod the code to look in the Settings Table.
This string is now in the Sorting and Grouping Window of my test report:
=StandardizePartNum([" & DLookup("Setting", "Settings") & "])
Now, when I run the report, it is asking for a Parameter Value for
" & DLookup("Setting", "Settings") & "
This makes sense, but I must have the syntax off.
Your original code string has two ampersands. The VB compiler barks at me
for some reason so I removed one of the ampersands that preceded DLookup in
the string.
Tom said:When I run this code from the On Open Event Procedure,
Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0) = "=StandardizePartNum([" & _
DLookup("Setting", "Settings") & "])"
End Sub
I get a Runtime error #2464 There is no sorting or grouping field or
expression defined for the goup level number you used.
You must precreate an entry in the Sorting and Grouping
window. I thought you said you had already done that??
Once you get the report sorting as you said it was, then add
the code to change it to the use the field from the settings
table. (Double check to make sure the record in the
Settings table has the name of the field you want to use.)
Yes, you need something/anything in Sorting and Grouping in
design view so the GroupLevel exists when you try to modify
it in the open event. I usually put an example expression
such as your original
=StandardizePartNum([Comp Part])
just as a reminder of what kind of thing I am doing. On
some ocassions I use something like
="Changed in Open event"
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.