relation problem between 2 table

  • Thread starter Thread starter Masoud
  • Start date Start date
M

Masoud

I have 2 table that called tbldiscon, Document list

tbldiscon has 3 fields:[contractor],[Discipline] , [Next]

[contractor] and [discipline] are text and primary key, [next] is number
with default value (0)

Document list table has these fields:

[Document Code],[subject],[originator],[data],[document type],[contractor],
[Discipline code],[Document Seq number] (all fields are text except [Document
Seq number] that is number and equal to [next] after update query)


[document code] field is concatenate of [Document type]="FSQ" AND
[CONTRACTOR] AND [Discipline Code] and [document seq number]

and [document seq number] also depend of value [contractor],[discipline
code] so for simillar [contractor] and [discipline code] should be increase
(+1) automaticly so i have used update query on tbldiscon and change value of
[next] to [next]+1 each time i inser new record in document list
table.([document seq number]=[next])

for excample:documet list should be like this:

document code contractor discipline Document Seq number date

FSQ-ARP-CIV-0001 ARP CIV 0001 03-FEB-09

FSQ-ARP-CIV-0002 ARP CIV 0002 03-FEB-09

FSQ-ARP-BLD-0001 ARP BLD 0001 03-FEB-09

FSQ-SHR-CIV-0001 SHR CIV 0001 03-FEB-09


and tbldiscon should be like this:

contractor discipline next

ARP civ 2
ARP BLD 1
SHR CIV 1

for updating these tables i have made form with one botton and on click
event for that botton.
fields like [originator],[discipline], [contractor] are combo box on form
wihout any code for them, [subject]
is text box and value of [document code] automaticly will be genrate when we
input the data in other fiedls in the form and push the
button.because that is concatenate of other fields.

query between 2 tables document list, tbldiscon, has relation one to many
for both

fields [contractor],[discipline].

my problem is when [discipline] and [contractor] are new for tbldiscon.the
code insert them in tbldiscon and update [next] to [next]+1 but document list
table will not be update. and it has "null" value for all the fields.

my click event code for that botton is like below:

Set dbs = CurrentDb
dbs.Execute "INSERT INTO tblDISCON ([Contractor],[DISCIPLINE],[Next]) " & _
"VALUES ( '" & Me.[Contractor] & "','" & Me.[Discipline Code] & "',0)"


[Originator Code].Enabled = False
[Document type].Enabled = False
DoCmd.SetWarnings False
If [Document type] = "PSI" Then DoCmd.OpenQuery "Update Next number
PSI", acViewNormal, acReadOnly Else DoCmd.OpenQuery "Update next number FSQ",
acViewNormal, acReadOnly


nextseq = [NEXT]

MsgBox nextseq
[Document seq number] = nextseq
If Len([NEXT]) = 1 Then fullseq = "000" & nextseq Else
If Len([NEXT]) = 2 Then fullseq = "00" & nextseq Else
If Len([NEXT]) = 3 Then fullseq = "0" & nextseq Else
If Len([NEXT]) = 4 Then fullseq = nextseq Else

answer = MsgBox("Your document number is " & [Document type] & "-" &
[Contractor] & "-" & [Discipline Code] & "-" & fullseq, 0, "Document Code")
[Document Code] = [Document type] & "-" & [Contractor] & "-" &
[Discipline Code] & "-" & fullseq

DoCmd.SetWarnings True

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
 
I do not know if this will be of help, but I noticed you used a field name of
[Next].
Next is a 'reserved' word in Access. You should always avoid using a
'reserved' word when choosing identifier names.

Reference: http://office.microsoft.com/en-us/access/HA100306431033.aspx

Good Luck.

Masoud said:
I have 2 table that called tbldiscon, Document list

tbldiscon has 3 fields:[contractor],[Discipline] , [Next]

[contractor] and [discipline] are text and primary key, [next] is number
with default value (0)

Document list table has these fields:

[Document Code],[subject],[originator],[data],[document type],[contractor],
[Discipline code],[Document Seq number] (all fields are text except [Document
Seq number] that is number and equal to [next] after update query)


[document code] field is concatenate of [Document type]="FSQ" AND
[CONTRACTOR] AND [Discipline Code] and [document seq number]

and [document seq number] also depend of value [contractor],[discipline
code] so for simillar [contractor] and [discipline code] should be increase
(+1) automaticly so i have used update query on tbldiscon and change value of
[next] to [next]+1 each time i inser new record in document list
table.([document seq number]=[next])

for excample:documet list should be like this:

document code contractor discipline Document Seq number date

FSQ-ARP-CIV-0001 ARP CIV 0001 03-FEB-09

FSQ-ARP-CIV-0002 ARP CIV 0002 03-FEB-09

FSQ-ARP-BLD-0001 ARP BLD 0001 03-FEB-09

FSQ-SHR-CIV-0001 SHR CIV 0001 03-FEB-09


and tbldiscon should be like this:

contractor discipline next

ARP civ 2
ARP BLD 1
SHR CIV 1

for updating these tables i have made form with one botton and on click
event for that botton.
fields like [originator],[discipline], [contractor] are combo box on form
wihout any code for them, [subject]
is text box and value of [document code] automaticly will be genrate when we
input the data in other fiedls in the form and push the
button.because that is concatenate of other fields.

query between 2 tables document list, tbldiscon, has relation one to many
for both

fields [contractor],[discipline].

my problem is when [discipline] and [contractor] are new for tbldiscon.the
code insert them in tbldiscon and update [next] to [next]+1 but document list
table will not be update. and it has "null" value for all the fields.

my click event code for that botton is like below:

Set dbs = CurrentDb
dbs.Execute "INSERT INTO tblDISCON ([Contractor],[DISCIPLINE],[Next]) " & _
"VALUES ( '" & Me.[Contractor] & "','" & Me.[Discipline Code] & "',0)"


[Originator Code].Enabled = False
[Document type].Enabled = False
DoCmd.SetWarnings False
If [Document type] = "PSI" Then DoCmd.OpenQuery "Update Next number
PSI", acViewNormal, acReadOnly Else DoCmd.OpenQuery "Update next number FSQ",
acViewNormal, acReadOnly


nextseq = [NEXT]

MsgBox nextseq
[Document seq number] = nextseq
If Len([NEXT]) = 1 Then fullseq = "000" & nextseq Else
If Len([NEXT]) = 2 Then fullseq = "00" & nextseq Else
If Len([NEXT]) = 3 Then fullseq = "0" & nextseq Else
If Len([NEXT]) = 4 Then fullseq = nextseq Else

answer = MsgBox("Your document number is " & [Document type] & "-" &
[Contractor] & "-" & [Discipline Code] & "-" & fullseq, 0, "Document Code")
[Document Code] = [Document type] & "-" & [Contractor] & "-" &
[Discipline Code] & "-" & fullseq

DoCmd.SetWarnings True

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
Click to expand...
 
I changed the field name [next], but it does not work yet.

thanks.

ND Pard said:
I do not know if this will be of help, but I noticed you used a field name of
[Next].
Next is a 'reserved' word in Access. You should always avoid using a
'reserved' word when choosing identifier names.

Reference: http://office.microsoft.com/en-us/access/HA100306431033.aspx

Good Luck.

Masoud said:
I have 2 table that called tbldiscon, Document list

tbldiscon has 3 fields:[contractor],[Discipline] , [Next]

[contractor] and [discipline] are text and primary key, [next] is number
with default value (0)

Document list table has these fields:

[Document Code],[subject],[originator],[data],[document type],[contractor],
[Discipline code],[Document Seq number] (all fields are text except [Document
Seq number] that is number and equal to [next] after update query)


[document code] field is concatenate of [Document type]="FSQ" AND
[CONTRACTOR] AND [Discipline Code] and [document seq number]

and [document seq number] also depend of value [contractor],[discipline
code] so for simillar [contractor] and [discipline code] should be increase
(+1) automaticly so i have used update query on tbldiscon and change value of
[next] to [next]+1 each time i inser new record in document list
table.([document seq number]=[next])

for excample:documet list should be like this:

document code contractor discipline Document Seq number date

FSQ-ARP-CIV-0001 ARP CIV 0001 03-FEB-09

FSQ-ARP-CIV-0002 ARP CIV 0002 03-FEB-09

FSQ-ARP-BLD-0001 ARP BLD 0001 03-FEB-09

FSQ-SHR-CIV-0001 SHR CIV 0001 03-FEB-09


and tbldiscon should be like this:

contractor discipline next

ARP civ 2
ARP BLD 1
SHR CIV 1

for updating these tables i have made form with one botton and on click
event for that botton.
fields like [originator],[discipline], [contractor] are combo box on form
wihout any code for them, [subject]
is text box and value of [document code] automaticly will be genrate when we
input the data in other fiedls in the form and push the
button.because that is concatenate of other fields.

query between 2 tables document list, tbldiscon, has relation one to many
for both

fields [contractor],[discipline].

my problem is when [discipline] and [contractor] are new for tbldiscon.the
code insert them in tbldiscon and update [next] to [next]+1 but document list
table will not be update. and it has "null" value for all the fields.

my click event code for that botton is like below:

Set dbs = CurrentDb
dbs.Execute "INSERT INTO tblDISCON ([Contractor],[DISCIPLINE],[Next]) " & _
"VALUES ( '" & Me.[Contractor] & "','" & Me.[Discipline Code] & "',0)"


[Originator Code].Enabled = False
[Document type].Enabled = False
DoCmd.SetWarnings False
If [Document type] = "PSI" Then DoCmd.OpenQuery "Update Next number
PSI", acViewNormal, acReadOnly Else DoCmd.OpenQuery "Update next number FSQ",
acViewNormal, acReadOnly


nextseq = [NEXT]

MsgBox nextseq
[Document seq number] = nextseq
If Len([NEXT]) = 1 Then fullseq = "000" & nextseq Else
If Len([NEXT]) = 2 Then fullseq = "00" & nextseq Else
If Len([NEXT]) = 3 Then fullseq = "0" & nextseq Else
If Len([NEXT]) = 4 Then fullseq = nextseq Else

answer = MsgBox("Your document number is " & [Document type] & "-" &
[Contractor] & "-" & [Discipline Code] & "-" & fullseq, 0, "Document Code")
[Document Code] = [Document type] & "-" & [Contractor] & "-" &
[Discipline Code] & "-" & fullseq

DoCmd.SetWarnings True

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
Click to expand...
Click to expand...
 
Thank you ,this 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)

Masoud said:
I changed the field name [next], but it does not work yet.

thanks.

ND Pard said:
I do not know if this will be of help, but I noticed you used a field name of
[Next].
Next is a 'reserved' word in Access. You should always avoid using a
'reserved' word when choosing identifier names.

Reference: http://office.microsoft.com/en-us/access/HA100306431033.aspx

Good Luck.

Masoud said:
I have 2 table that called tbldiscon, Document list

tbldiscon has 3 fields:[contractor],[Discipline] , [Next]

[contractor] and [discipline] are text and primary key, [next] is number
with default value (0)

Document list table has these fields:

[Document Code],[subject],[originator],[data],[document type],[contractor],
[Discipline code],[Document Seq number] (all fields are text except [Document
Seq number] that is number and equal to [next] after update query)


[document code] field is concatenate of [Document type]="FSQ" AND
[CONTRACTOR] AND [Discipline Code] and [document seq number]

and [document seq number] also depend of value [contractor],[discipline
code] so for simillar [contractor] and [discipline code] should be increase
(+1) automaticly so i have used update query on tbldiscon and change value of
[next] to [next]+1 each time i inser new record in document list
table.([document seq number]=[next])

for excample:documet list should be like this:

document code contractor discipline Document Seq number date

FSQ-ARP-CIV-0001 ARP CIV 0001 03-FEB-09

FSQ-ARP-CIV-0002 ARP CIV 0002 03-FEB-09

FSQ-ARP-BLD-0001 ARP BLD 0001 03-FEB-09

FSQ-SHR-CIV-0001 SHR CIV 0001 03-FEB-09


and tbldiscon should be like this:

contractor discipline next

ARP civ 2
ARP BLD 1
SHR CIV 1

for updating these tables i have made form with one botton and on click
event for that botton.
fields like [originator],[discipline], [contractor] are combo box on form
wihout any code for them, [subject]
is text box and value of [document code] automaticly will be genrate when we
input the data in other fiedls in the form and push the
button.because that is concatenate of other fields.

query between 2 tables document list, tbldiscon, has relation one to many
for both

fields [contractor],[discipline].

my problem is when [discipline] and [contractor] are new for tbldiscon.the
code insert them in tbldiscon and update [next] to [next]+1 but document list
table will not be update. and it has "null" value for all the fields.

my click event code for that botton is like below:

Set dbs = CurrentDb
dbs.Execute "INSERT INTO tblDISCON ([Contractor],[DISCIPLINE],[Next]) " & _
"VALUES ( '" & Me.[Contractor] & "','" & Me.[Discipline Code] & "',0)"


[Originator Code].Enabled = False
[Document type].Enabled = False
DoCmd.SetWarnings False
If [Document type] = "PSI" Then DoCmd.OpenQuery "Update Next number
PSI", acViewNormal, acReadOnly Else DoCmd.OpenQuery "Update next number FSQ",
acViewNormal, acReadOnly


nextseq = [NEXT]

MsgBox nextseq
[Document seq number] = nextseq
If Len([NEXT]) = 1 Then fullseq = "000" & nextseq Else
If Len([NEXT]) = 2 Then fullseq = "00" & nextseq Else
If Len([NEXT]) = 3 Then fullseq = "0" & nextseq Else
If Len([NEXT]) = 4 Then fullseq = nextseq Else

answer = MsgBox("Your document number is " & [Document type] & "-" &
[Contractor] & "-" & [Discipline Code] & "-" & fullseq, 0, "Document Code")
[Document Code] = [Document type] & "-" & [Contractor] & "-" &
[Discipline Code] & "-" & fullseq

DoCmd.SetWarnings True

Exit_Command26_Click:
Exit Sub

Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
Click to expand...
Click to expand...
Click to expand...
 
Back
Top