Autonumber problems.

[kruger101]

Hi guys
I've had trouble with this for quite a while now. Can someone please help me?

I have 1 autonumber, Refnumber, which is sequential and it's my primary key. I then have a Combo box selection field, RefDistrict. This is a string field, with the selections being: DFE; DPZ; DAB; DGG; DAS; DTE.

I would like to have the end result be
RefNumber = 1
RefDistrict = DFE
Thus New Field = DFE1
Then
RefNumber = 2
RefDistrict = DGG
New Field = DGG1
Then
RefNumber = 3
RefDistrict = DFE
New Field = DFE2
Then
RefNumber = 4
RefDistrict = DAB
New Field = DAB1.

etc etc.

I don't know what to do.
Can someone please help me with ideas? Thanks a whole lot.

 

[RCurtin]

What do you want to do with the new value? Assuming you want to put it in a textbox:
txtNewField = me.cboMyComboBox.value & RefNumber

 

[kruger101]

RCurtin
Thanks for the reply. But you don't understand completely, if you choose a new District, the Refnumber should reset!! That's what my problem is. And I don't know how to do it. And the result of the new field - I want to store it in a table for later reference.

 

[statsman]

When you select autonumber as your field type you have a unique sequential number for each record. You can't repeat numbers.

 

[neileg]

Do a search in these forums about setting your own incrementing number using DMax().

 

[kruger101]

neileg
I used DCount to count the number of times say, "DPR" has been selected, but now I get the total amount of times DPR has been used. ie.
RefNumber is at 14
DPR has been selected 7 times.
Now even though RefNumber nr. 5 is the second time DPR has been selected, it displays the total number of DPR's. In stead of showing DPR2, it shows DPR7.

Any ideas? Thanks a lot for your help.

 

[kruger101]

1. DCount() is the wrong function to use for this purpose. Use DMax(). You want the maximimum value used, not the number of records.
2. You should NOT be storing text and numbers in the same column. You are looking at nothing but trouble. Your next question will be why is DFE2 sorting AFTER DFE11.
3. Please do not start new threads on the same topic. If you don't understand the answer, ask for clarification.

Pat
But I think I do need the number of records, that's why I used DCount in stead of DMax. My reasoning: I want to know the number of times say DFE is picked, so if DFE has been picked 2 times, and we are at record 6 and DFE gets picked again, it's 2+1=3. But the thing is, if the answer turns to DFE3, the previous record that supposed to be DFE2 also turns into DFE3.

I'm sorry if I'm ignorant but I can't understand why you say I should use DMax? DMax gets the max value. So if I get the Max value, what do I do then?

Thanks Pat.

 

[neileg]

So if I get the Max value, what do I do then?

You add 1.

 

[kruger101]

This is what I came up with

Private Sub Form_Current()
If Me.NewRecord Then
If Me.RefNumberDistrict = "DPR" Then
Me!RefDPR.Value = Nz(DMax("[RefDPR]", "Combined_tbl"), 0) + 1
End If
If Me.RefNumberDistrict = "DPZ" Then
Me!RefDPZ.Value = Nz(DMax("[RefDPZ]", "Combined_tbl"), 0) + 1
End If
End If
End Sub

I can't get it to work. The part about the Me.NewRecord is in because, when I scroll through records, the RefDPR and RefDPZ value don't change. I tried this, and when it isn't in, the value keeps changeing as if you are selecting the RefNumberDistrict again.

Any help is greatly appreciated.
kruger101