obtain sequence number automaticly when depend on value of other f

[Masoud]

Hello,
I have one document list , that there are some fields like doc no,
contractor name, discipline, sequence No, date
Doc no field is concatenate of “TQâ€+[CONTRACTOR NAME]+DISCIPLINE+SEQUENCE NO
FOR EXCAMPLE

DOC NO CONTRACTOR NAME DISCIPLINE SEQUENCE No date
Tq-arp-civ-0001 arp civ 0001 30-jan-09

Tq-arp-civ-0002 arp civ 0002 30-jan-09

Tq-arp-blg-0001 arp blg 0001 30-jan-09


i have made another that table that there are these fields discipline,
contractor name, next
Discipline and contractor name are primary key and they have relation to
table “document listâ€
[next] has default value 0 in this table
Now i want to make coding in my form that concatenate fields for making doc
no field and obtain sequence no automaticly that depend
on both of field [contractor name], [discipline] for example if one of them
for the first time changed sequence no started from ooo1
like below

DOC NO CONTRACTOR NAME DISCIPLINE SEQUENCE N date
Tq-arp-civ-0001 arp civ 0001 30-jan-09

Tq-arp-civ-0002 arp civ 0002 30-jan-09

Tq-arp-blg-0001 arp blg 0001 30-jan-09

Tq-arp-civ-0003 arp civ 0003 30-jan-09

Tq-shr-civ-0001 shr civ 0001 30-jan-09

Tq-shr-civ-0002 shr civ 0002 30-jan-09

I did when sequence no just depend on changing of one field but i can not
do when depend on two field
Please help me?
Thanks.

 

[Clifford Bass]

Hi Masoud,

Try something like:

txtSequenceNo = Nz(DLookup("SEQUENCE NO", "YOUR_TABLE_NAME", _
"[CONTRACTOR NAME] = """ & Replace(txtContractorName, _
"""", """""") & """ and DISCIPLINE = """ & Replace( _
txtDiscipline, """", """""") & """"), 0)

You will need to modify the table name. txtSequenceNo is the text box
that contains the sequence number. txtContractorName is the text box that
contains the contractor name. txtDiscipline is the text box that contains
the discipline. The Replace() function is used to make sure that should
there happen to be a quote (") within the contractor name or discipline, that
it will not cause a problem.

Hope that helps,

Clifford Bass

 

[Masoud]

Thank you,your function worked correctly.before I had used query (behind of
my form) between 2 table
document list, tbldiscon just for extracting [next] value for each record

now this function works instead of query, so I deleted tbldiscon (and both
relations) of my query and just there is
document list table behind of my form.

txtSequenceNo = Nz(DLookup("[next]", "tbldiscon", _
"[CONTRACTOR] = """ & Replace(txtContractorName, _
"""", """""") & """ and DISCIPLINE = """ & Replace( _
txtDiscipline, """", """""") & """"), 0)

Hi Masoud,

Try something like:

txtSequenceNo = Nz(DLookup("SEQUENCE NO", "YOUR_TABLE_NAME", _
"[CONTRACTOR NAME] = """ & Replace(txtContractorName, _
"""", """""") & """ and DISCIPLINE = """ & Replace( _
txtDiscipline, """", """""") & """"), 0)

You will need to modify the table name. txtSequenceNo is the text box
that contains the sequence number. txtContractorName is the text box that
contains the contractor name. txtDiscipline is the text box that contains
the discipline. The Replace() function is used to make sure that should
there happen to be a quote (") within the contractor name or discipline, that
it will not cause a problem.

Hope that helps,

Clifford Bass

Hello,
I have one document list , that there are some fields like doc no,
contractor name, discipline, sequence No, date
Doc no field is concatenate of “TQâ€+[CONTRACTOR NAME]+DISCIPLINE+SEQUENCE NO
FOR EXCAMPLE

DOC NO CONTRACTOR NAME DISCIPLINE SEQUENCE No date
Tq-arp-civ-0001 arp civ 0001 30-jan-09

Tq-arp-civ-0002 arp civ 0002 30-jan-09

Tq-arp-blg-0001 arp blg 0001 30-jan-09


i have made another that table that there are these fields discipline,
contractor name, next
Discipline and contractor name are primary key and they have relation to
table “document listâ€
[next] has default value 0 in this table
Now i want to make coding in my form that concatenate fields for making doc
no field and obtain sequence no automaticly that depend
on both of field [contractor name], [discipline] for example if one of them
for the first time changed sequence no started from ooo1
like below

DOC NO CONTRACTOR NAME DISCIPLINE SEQUENCE N date
Tq-arp-civ-0001 arp civ 0001 30-jan-09

Tq-arp-civ-0002 arp civ 0002 30-jan-09

Tq-arp-blg-0001 arp blg 0001 30-jan-09

Tq-arp-civ-0003 arp civ 0003 30-jan-09

Tq-shr-civ-0001 shr civ 0001 30-jan-09

Tq-shr-civ-0002 shr civ 0002 30-jan-09

I did when sequence no just depend on changing of one field but i can not
do when depend on two field
Please help me?
Thanks.

 

[Clifford Bass]

Hi Masoud,

Glad to hear it helped! You are welcome.

Clifford Bass