you're welcome
happy to help
Enjoy your vacation!
(it would be good to print a relationship diagram and take
that with you -- make sure and stretch out all fieldlists,
include all tables even if they don't have relationships,
enforce referential integrity, and put the "1" side of the
relationship on the left and the "many" side on the right --
so your data flows)
Here are some things to think about when you come back
A Long Integer takes 4 bytes to store. Autonumbers are Long
Integers that Access makes up the data for.
A text field takes about 1 byte per character -- so, if you
have a name that is 35 characters, you are using about ten
times the storage that a long integer would take...and
comparing ten times the number of bytes -- for each record!
What happens if someone puts an extra space somewhere or
changes the spelling of a name or part number?
Using text to link is not efficient or reliable -- and when
you have a large volume of data, these are major considerations.
Not only do Long Integers take less space to store, but they
do not have the problem with spaces that text fields have.
You should never use user-data to provide the
behind-the-scenes links. User-data also changes -- then you
have to worry about updating all the related references.
Part Number should be a descriptive field in the Parts table
and that is the only place it should be stored -- PartID
should be used to locate records and as a foreign key in
related tables.
Here is how to change foreign text IDs to use a numeric field
~~~~~~~~~~~~~~~~
QUERY TO UPDATE RELATED IDs
you are storing a text field in a related table that relates
to a text field in the main table
now, you have added a long integer ID in the related table
and want to populate it with the IDs from the main table
make a new query based on the table you want to change
add the main table to your query
link the two tables on the common text field
change the query type from a Select Query to an Update Query
from the menu bar --> Query, Update
on the grid:
field --> ID_fieldname
table --> related_tablename
UpdateTo --> main__tablename.ID_fieldname
then, RUN (!) your query
a quick way check to ensure that all related IDs were filled
out (without writing comparison query) is:
1. open related table
2. sort by common text field
3. visually scan ID field to make sure it is filled out for
every record where there was text
Naturally, if you have thousands of records, you can design
a query to show records where text Is Not Null and ID_field
Is Null
-- then, add those unmatched text values to the main table
OR edit them so text text matches and run query again and/or
manually fill IDs
Then, when you are satisfied that all data has been linked,
delete the text field from the related table
compact/repair database to regain the space it was using
of course, back up your database before running any action
queries on it
~~~~~~~~~~~~~`
Once you are using PartID, it will be the first column of
your combobox. You can hide it by setting its Column Width
to 0 in the ColumnWidths property for the combo or listbox.
Press F1 in any property to get help
Warm Regards,
Crystal
Microsoft Access MVP 2006
remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day
Thank you ever so much! Yes, my day has started out awesome due to your
thorough response. Today is my last day of work before a two week vacation
so I may not have a chance to tackle this today, but will as soon as I get
back. Exciting!
Regarding your question about the primary key. No particular reason --
however, it seemed during design , and I could not tell you at this moment
how, that autonumber as the primary key caused me a bit of grief. So, I have
discarded it in most of my tables. However, I sense some significance in
your question so, if you could give me some insight or more guidance
regarding the ins and outs of using auto number or point me to some clear,
layperson reading, I'd love to explore it futher.
Again, my sincerest thanks!
:
Hi ztyco,
You are already changing the SQL to show the list ... take
it one step further ...
Create an option frame on your main form for how the Find
List will be sorted (and, optionally, what it will show)
Name --> FraSort_IndParts
Default value --> 1
Option buttons
Value, Label Caption
1, "Cash Part #"
2, "Customer Part #"
Modify your current Sub behind the form to be a Function so
that it can be specified in an event on the property sheet.
on the AfterUpdate event of the FraSort_IndParts -->
= lstIndividualPartsSQL()
'~~~~~~~~~~~~~~
Private function lstIndividualPartsSQL()
Dim strSQL As String
if isNull(me.txtCustNumber) then
exit function
end if
strSQL = "SELECT [Cash Part No], " _
& " [Set Pressure], [Cust Part No], " _
& " [Model Family], " _
& " [Inlet Connection Size], " _
& " Service, " _
& " [Special Requirement], " _
& " Price, " _
& " ListPrice," _
& " [Single Model Multiplier], " _
& " [Commission Rate], " _
& " [Special Notes] " _
& " FROM " _
& " [Query - Special Pricing Main & Individual] " _
& " WHERE (Cust_Cust_ID = '" _
& Me.txtCustNumber _
& " & "') " _
& " ORDER BY "
if me.FraSort_IndParts = 2 then
strSQL = strSQL & " [CustPartNo_fieldname];"
else
strSQL = strSQL & " [Cash Part No];"
end if
'remove next line after debugged
debug.print strSQL
Me!lstIndividualParts.RowSource = strSQL
Me!lstIndividualParts.Requery
End Sub
'~~~~~~~~~~~~~~
Private Sub lstIndividualParts_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FRMSpecialPricingIndividual"
if not isNull(Me.txtCustNumber) then
stLinkCriteria = "([Cust No] = '" _
& Trim(Me.txtCustNumber.Value) _
& "' "
end if
if not isNull(Me.Me.lstIndividualParts) then
if len(stLinkCriteria ) > 0 then
stLinkCriteria = stLinkCriteria & " AND "
end if
stLinkCriteria = "([Cash Part No] = '" _
& Trim(Me.Me.lstIndividualParts) _
& "' "
end if
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
'~~~~~~~~~~~~~~
Why are you using txtCustNumber (text) instead of CustomerID
(autonumber) as a primarky key for your table? It is best
to use the autonumber field or a long integer for a primary
key. It is also good to use a meaningless value for the user.
Warm Regards,
Crystal
Microsoft Access MVP 2006
remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day
I have the below coding that populates a list box but sorts it by our Part
Number.
I would like to add a button that can be clicked that will sort the
information in the fill list by Customer Part Number.
Could someone please let me know who to go about doing that? Thank you.
========================================
Also, can someone provide me some guidance regarding how to customize the
headers on the FillList. Right now, it just has the default header. I would
like to customize them.
========================================
Thanks ever so much for your help.
========================================
Private Sub FillList()
Dim strSQL As String
Dim strCustomerNumber As String
strCustomerNumber = Me.txtCustNumber.Value
strSQL = "SELECT tblSpecPrcModelFamily.[Model Family],
tblSpecPrcModelFamily.Multiplier "
strSQL = strSQL & "FROM tblSpecPrcModelFamily "
strSQL = strSQL & "WHERE (((tblSpecPrcModelFamily.[Customer Number]) =
'" & strCustomerNumber & "'))"
strSQL = strSQL & "ORDER BY tblSpecPrcModelFamily.[Model Family];"
strSQL = "SELECT [Cash Part No], [Set Pressure], [Cust Part No], [Model
Family], [Inlet Connection Size], "
strSQL = strSQL & "Service, [Special Requirement], Price, ListPrice,
[Single Model Multiplier], [Commission Rate], [Special Notes] "
strSQL = strSQL & "FROM [Query - Special Pricing Main & Individual] "
strSQL = strSQL & "WHERE (Cust_Cust_ID = '" & strCustomerNumber & "')"
strSQL = strSQL & "ORDER BY [Cash Part No];"
Me!lstIndividualParts.RowSource = strSQL
Me!lstIndividualParts.Requery
End Sub
Private Sub lstIndividualParts_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim strCustomerNumber As String
Dim strPartNumber As String
strCustomerNumber = Trim(Me.txtCustNumber.Value)
strPartNumber = Me.lstIndividualParts.Value
stDocName = "FRMSpecialPricingIndividual"
stLinkCriteria = "([Cust No] = '" & strCustomerNumber & "') AND ([Cash
Part No] = '" & strPartNumber & "')"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
