Dynamic key Based on User Input - Is this even Logical?

[richy240]

I am exploring the possibility of storing a key value in multiple fields, but presenting it to the user as one single value. The reason for this is that the information used as the primary key contains the value of a foreign key field plus some additional data. I would like to limit the number of times this data is listed in the table.

The table structure is as follows:

- po_SAP_num (primary key; relates to foreign table)
- ncr_num (primary key)
- supplier_SAP_num
- agency_id
- date_opened
- date_closed

The format for po_SAP_num is 10 digits, starting with '51' (ex. 51000012345). The format for ncr_num is based on the po_SAP_num field. It adds 'NCR' to the beginning of po_SAP_num, a dash, and a three digit code. This three digit code is actually the unique piece of data here. It increments every time there is a new record for that po_SAP_num (ex. NCR5100012345-001, ...002, ...003, etc.).

So, I want to display the 'NCR...' number to the user as NCR5100012345-001, but store it in two different fields: po_SAP_num and another field, maybe ncr_num; one containing a number (that can be altered and displayed as a three digit number, padded with zeros).

I face a few problems here:
- When the user inputs po_SAP_num, I must (using code) count the number of records in the DB with po_SAP_num, then build a string that has 'NCR', the value of po_SAP_num and the next available report number padded it with zeros on the right side. I must also present this to the user before the data input form is closed.
- When the user wants to run a report, I want him/her to input the whole string (ex. NCR5100012345-001) as the criteria. Then I must (again, using code) dismantle this critera into usable data (two values in this case) in order to run the report.

This is a large and complicated undertaking, I think. Right now I am just looking for help with the logic behind this problem. After that, I think I might be able to implement this without too many hicups (I hope).

So, my questions are:
- Is this even a good idea?
- Would it be more work than it is worth?
- Would this either speed up or slow down my application (to the point where it is noticable)?
- Am I crazy for even thinking this?

Thanks in advance! Anyone who would touch this one is truely a helpful individual!

 

[Pat Hartman]

I would keep three fields, "NCR", po_SAP_num, NCR_Seq. Is it really necessary to mush the fields for user input? If you could get them to input the fields separately, you wouldn't need any coding at all.

 

[richy240]

Yeah - the numbers will be input separately anyway. I probably didn't explain this as well as I could have...

I am just looking to display this data to the user as if it were in NCR5100000001-001 format; I do not want to store this information in this format. I would like to store it as the purchase order number and a sequential number representing the next report number of that purchase order number:

- PO# 5100000001; NCR# 001
- PO# 5100000002; NCR# 001
- PO# 5100000002; NCR# 002
- PO# 5100000003; NCR# 001
- PO# 5100000003; NCR# 002
- PO# 5100000003; NCR# 003
.
.
.
- PO# 5100000009; NCR# 001

As you can see, the information will be stored regularly. The information will be displayed to the user as NCR5100000001-001. I would also like the user to be able to input this when running a report. I would, using code, dismantle the input and turn it into usable criteria.

Otherwise, I am going to have to store the PO number (5100000001), the sequential report number (001) and the entire NCR number (NCR5100000001-001). This, to me, seems like a lot of wasted space. I would also like to control the information the user inputs as closely as possible as well.

Thanks.

 

[doulostheou]

I think any difference in performance caused by assembling/dismantling this value would be negligible. If you know for sure that all of the numbers will always be the same length, the code for creating the value and dismantling it would be rather simple.

As long as you don't plan to store this number in your table in place of the real key values, there are no major concerns behind your code except for verifying that what you are doing will indeed be easier for your users. Your best bet for determining this is asking them.

Sub Test()
    Dim NewKey As String
    Dim lngPO As Double
    Dim intNCR As Integer
    
    NewKey = TransformValue("5100000001", "001")
    
    MsgBox NewKey
    
    'Dismantle Value
    lngPO = Mid(NewKey, 4, 10)
    intNCR = Right(NewKey, 3)
    
    MsgBox "PO# " & lngPO & "; NCR# " & Format(intNCR, "000")
End Sub

Function TransformValue(PO As Double, NCR As Integer) As String
    TransformValue = "NCR" & PO & "-" & Format(NCR, "000")
End Function

Let me know if I have oversimplified your situation.

 

[richy240]

No - at first glance, it seems that this would work for piecing together the number... In your opinion, is this a good (or bad) way of doing things? Should I just make the user enter the number himself (and risk mistakes)?

I am getting recommendations from every direction, as I have posted this question on a couple Access forums. Everyone is saying, "do it this way, " or, "don't do that, do this." You are the first person to actually offer constructive advise. Thank you!

I am going to start piecing this together. I will post again and let you know how it works out.

Thanks again for your help!

 

[richy240]

Oh! Can you tell me where I can find information on the Format() function?

I looked in Access' VB help, but I couldn't find any good information...

Thanks.

 

[richy240]

Also, when would be the best time to run something of this nature? I have some code in the OnExit event, but this might even be in the wrong place. (It works, but is this the most ideal time to run it?)

Thanks.

 

[Pat Hartman]

If you have a field called PONum (I hope it really doesn't have a pound sign or spaces in it) and a field called NCRNum (same comment), then you don't need to store the concatenaed value.

To get help for a function, you need to open help when you are in the VBA window. There is a lot of info available for the Format() function but you need to take the "See Also" path to get to it.

You would concatenate the two fields in queries so the concatenated value can be shown on forms and reports. If you concatenate the fields in the query, you won't need any event code.

The OnExit event would be inappropriate for this in any event. You would not use event code at all. The fields would be concatenated in the controlSource of an unbound control.

="NCRS" & PONum & "-" & NCRNum

 

[doulostheou]

Pat's right. If you do this at the query level, it will be much easier to lookup records, etc. I don't know if there would be a performance gain/loss either way, but your life will be easier by doing it in the query.

If you were to use VBA, you would use the OnCurrent event to run the TransformValue function (this will update the display every time the user goes to a new record). If you allow them to update these numbers while they are on the record, you would have to also run the function on the AfterUpdate event of all applicable fields. To do any sort of lookup, you would have to dismantle the user's input on whatever event triggers it.

Again, take Pat's suggestion as it will save you a lot of time.