Duplicating

G

Guest

Using the duplication button speeds data entry tremendously. Is there any way
to duplicate and auto change a "indexed (no duplicates)" field, (it is text)?
The wholes form revolves around that field, which is comprised of letters and
numbers, and I would rather not have duplicates of it.
 
J

John Vinson

Using the duplication button speeds data entry tremendously. Is there any way
to duplicate and auto change a "indexed (no duplicates)" field, (it is text)?
The wholes form revolves around that field, which is comprised of letters and
numbers, and I would rather not have duplicates of it.
Click to expand...

You'll need to give us some help here. Certainly, it can be changed,
probably using a small VBA function; but since you don't say what the
field looks like, what values currently exist, and how it should be
changed, it's more than a bit difficult to suggest just how!

I'd suggest putting the code to calculate the new value in the Form's
BeforeInsert event; it would need to use DLookUp() to find the
previous entry and then some algorithm (depending on the content of
the field) to generate the new value.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

The field is a 17 digit text field example "W915BQ43220851XXX" . If you have
ever been in the military regardless of branch, you will recognize this as a
TCN # or TMR #. If not, no big deal, this is a transportation code for cargo,
air cargo in this case. I work for the Army, preparing and transporting air
cargo on the Air Force cargo jets. What I have developed is a Access program
to accomplish the paperwork in a more efficient manner. Using the "Duplicate"
button helps speeds this process. The problem I have is this TMR/TCN cannot
be a duplicate once used it is never good again. Ideally I would have a
button to duplicate al of the cargo information except the TCN/TMR or have it
automatically change the TCN/TMR even if it is the last digit being the "X"
changing to a numeric value that increased per duplicate. Current I have the
table properties for this field set to duplicates ok, but I really shouldn't
have it set that way. Unfortunately being self Access taught I do not posses
the knowledge, YET, to write a VBA to expound on the current capabilities of
Access. If you can provide guidance I would greatly appreciate it.
Steve
 
J

John Vinson

The field is a 17 digit text field example "W915BQ43220851XXX" . If you have
ever been in the military regardless of branch, you will recognize this as a
TCN # or TMR #. If not, no big deal, this is a transportation code for cargo,
air cargo in this case. I work for the Army, preparing and transporting air
cargo on the Air Force cargo jets. What I have developed is a Access program
to accomplish the paperwork in a more efficient manner. Using the "Duplicate"
button helps speeds this process. The problem I have is this TMR/TCN cannot
be a duplicate once used it is never good again. Ideally I would have a
button to duplicate al of the cargo information except the TCN/TMR or have it
automatically change the TCN/TMR even if it is the last digit being the "X"
changing to a numeric value that increased per duplicate. Current I have the
table properties for this field set to duplicates ok, but I really shouldn't
have it set that way. Unfortunately being self Access taught I do not posses
the knowledge, YET, to write a VBA to expound on the current capabilities of
Access. If you can provide guidance I would greatly appreciate it.
Steve
Click to expand...

Well, it will require some VBA code to determine the last "x" and take
appropriate action. If the last digit is 3 then you could increase it
to 4 - but what if it's 9? or worse, if the last three digits are 999?
What if you increment the value and still get a duplicate: how should
the program respond?

Here's some suggested code that will *partially* resolve the issue. It
will find the largest existing TCN/TMR value in the table (which might
not be the most recently entered one!), increment the last three
digits, and pop a message box warning of a duplicate if that's not
possible or if doing so generates another duplicate. Open your Form in
design view, view its Properties, select the BeforeInsert event on the
Events tab, and invoke the Code Builder. Adjust fieldnames as needed
to match your database.

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strTCN As String
Dim iSeq As Integer
strTCN = DMax("[TCN_TMR]", "[YourTable]")
iSeq = Right([strTCN], 3) ' extract last three bytes, convert to num
If iSeq < 999 Then
' build a new TCN by incrementing value and concatenating string
strTCN = Left(strTCN, 14) & Format(iSeq + 1, "000")
Else
MsgBox "Ran out of numbers, hand-assign TCN/TMR", vbOKOnly
Exit Sub
End If
If IsNull(DLookUp("[TCN/TMR]", "yourtable", "[TCN/TMR] = '" _
& strTCN & "'") Then ' If the new value doesn't exist, add it
Me!txtTCN_TMR = strTCN ' use your own textbox name of course
Else
MsgBox "Autogenerated TCN/TMR causes a duplicate, enter manually", _
vbOKOnly
End If
End Sub

Air code, untested - post back if it doesn't work.

And... good luck and God bless you and all the troops you're
supporting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
G

Guest

Going to give a try today, thank you. Guess I need to teach myself VBA, I see
it can take me way beyond the capabilities of "just" Access. Can you
recommend any books?

Steve

John Vinson said:
The field is a 17 digit text field example "W915BQ43220851XXX" . If you have
ever been in the military regardless of branch, you will recognize this as a
TCN # or TMR #. If not, no big deal, this is a transportation code for cargo,
air cargo in this case. I work for the Army, preparing and transporting air
cargo on the Air Force cargo jets. What I have developed is a Access program
to accomplish the paperwork in a more efficient manner. Using the "Duplicate"
button helps speeds this process. The problem I have is this TMR/TCN cannot
be a duplicate once used it is never good again. Ideally I would have a
button to duplicate al of the cargo information except the TCN/TMR or have it
automatically change the TCN/TMR even if it is the last digit being the "X"
changing to a numeric value that increased per duplicate. Current I have the
table properties for this field set to duplicates ok, but I really shouldn't
have it set that way. Unfortunately being self Access taught I do not posses
the knowledge, YET, to write a VBA to expound on the current capabilities of
Access. If you can provide guidance I would greatly appreciate it.
Steve
Click to expand...

Well, it will require some VBA code to determine the last "x" and take
appropriate action. If the last digit is 3 then you could increase it
to 4 - but what if it's 9? or worse, if the last three digits are 999?
What if you increment the value and still get a duplicate: how should
the program respond?

Here's some suggested code that will *partially* resolve the issue. It
will find the largest existing TCN/TMR value in the table (which might
not be the most recently entered one!), increment the last three
digits, and pop a message box warning of a duplicate if that's not
possible or if doing so generates another duplicate. Open your Form in
design view, view its Properties, select the BeforeInsert event on the
Events tab, and invoke the Code Builder. Adjust fieldnames as needed
to match your database.

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strTCN As String
Dim iSeq As Integer
strTCN = DMax("[TCN_TMR]", "[YourTable]")
iSeq = Right([strTCN], 3) ' extract last three bytes, convert to num
If iSeq < 999 Then
' build a new TCN by incrementing value and concatenating string
strTCN = Left(strTCN, 14) & Format(iSeq + 1, "000")
Else
MsgBox "Ran out of numbers, hand-assign TCN/TMR", vbOKOnly
Exit Sub
End If
If IsNull(DLookUp("[TCN/TMR]", "yourtable", "[TCN/TMR] = '" _
& strTCN & "'") Then ' If the new value doesn't exist, add it
Me!txtTCN_TMR = strTCN ' use your own textbox name of course
Else
MsgBox "Autogenerated TCN/TMR causes a duplicate, enter manually", _
vbOKOnly
End If
End Sub

Air code, untested - post back if it doesn't work.

And... good luck and God bless you and all the troops you're
supporting.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
J

John Vinson

Going to give a try today, thank you. Guess I need to teach myself VBA, I see
it can take me way beyond the capabilities of "just" Access. Can you
recommend any books?
Click to expand...

Anything written by John Viescas; or if you're ready for the Deep End
School of Swimming, _Access XP Developer's Handbook_ by Getz et al.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 

Ask a Question

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.

Ask a Question

Similar Threads

Prevent Duplicate Form Entries 0
The changes you requested to the table werenot successful ........ 1
Access Auto Matching Duplicates? 0
Duplicate Subform and SubSubForm Records but not Main 1
Linking Tabs 3
Form Field - No Duplicate 3
Duplicate Entry 5
Form refuses to open 1

Top