This will make sense if you read the whole message

[Guest]

Hi,

My databse is called Supplier Scoreacrd. I have a Master table which
contains the following fileds: Supplier, Qty CAR, Qty NCR, Qty Backlogs,
Month. I then created 2 separate input forms, Quality & Materials.

In the Quality form, I used a Query which brings up Supplier, Qty CAR, Qty
NCR & Month.

In the Materials form, I used a Query which brings up Supplier, Qty Backlogs
and Month.

I set up the database such that the Operator uses the Quality input form if
he is from the Quality departmnet and Materials input if he is from
Materials. In the main Switchboard, the operator clicks either Materials or
Quality. This then takes him to another screen which prompts him to choose a
Month from a drop down.

When the month is chosen, another Form opens with a list of all the
Suppliers and the rest of the fields for that form. The month chosen appears
in each field under the column Month for each Supplier. The person enters the
data for Qty CAR, etc.

When he is finish, he clicks an "Update" button which appends the fields in
the form to the Master table.

The problem I have is since there are 2 different users "Quality &
Materials" and they are both choosing the same Month for entry, when they
click update, the record is being populated twice for the month i.e.

The Master table should reflect the scores as such: Supplier, NCR, CAR,
Backlogs, Month.
If Supplier, NCR, CAR & Month is there from the Quality input, then when
Materials input Backlogs for the same Month, these fields get populated in
another row below with the same month.

How do I get the database to append the info in the same row without
creating duplicate records for the same month?

Anyone have any ideas?

 

[Guest]

There is a simple way to answer this. I am no MVP, but I know what works,
when needing to do the littliest work to get something done. In your Update
button, you simply need to have code that will in part search for a value for
the month. I use the Dlookup & Dcount formula alot, to routinely check for
values in fields, to make sure there is nothing there. Exp:

if (Dlookup("[Month]","Quality & mat","[month]=something") then
Exit Sub
end if

Now if you know there is something that is going to be populated, you can
routinely look or count to see if that value is there. If it's there, then
exit. If it's not there, then update. I hope this helps. Many people use
fancy ways of doing things, but sometimes it's just the simpliest ugly ways
that often work best. Good luck!

 

[Guest]

In each form check first whether the row already exists in the table. If not
insert a new row into the table, otherwise update the existing row. You
don't say what data types the columns are so I'll assume Supplier is text and
Qty CAR, Qty NCR, Qty Backlogs and Month are all numbers.

So for the quality form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute


For the materials form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty Backlogs],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty Backlogs] & "," & Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty Backlogs] = " & Me.[Qty Backlogs] & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

If the data types of the columns are not as I've assumed you'll need to
amend the expression assigned to the strCriteria variable , and those for the
SQL statements so that the values are correctly delimited with quotes or not
as appropriate. In the above only the Supplier value is delimited with
quotes, using a pair of quotes characters within each string expression,
which is interpreted by Access as a literal quotes character. Alternatively
you can use a single single-quote character, e.g.

strCriteria = "Supplier = '" & Me.Supplier & "' And " & _
"Month" = & Me.Month

or the Chr(34) function:

strCriteria = "Supplier = " & Chr(34) & Me.Supplier & Chr(34) & " And " & _
"Month" = & Me.Month

Ken Sheridan
Stafford, England

 

[Guest]

Ken:

You seem to understand what I want and I believe you have the answer for me.
To be honest with you, I have zero SQl experience but I am willing to try
anything. See if this makes sense:

I have a table called [Master Input Table]. I ran 2 queries out of this
table to get either the info I need for the Quality form or the Materials
Form. I named the queries Materials and Quality, respectively. I built the
forms from these queries with the data as I outlined in my 1st email. These
forms will be used to append the data to a table called [Appended Master].

The field names that are in the [Appended Master] Table being populated
through the Quality & Materials forms are: Supplier Code (text), Supplier
Name (text), Qty CAR, Qty NCR, Qty Backlogs, Month (text). It is the same
fields in the [Master Input Table]

I clicked on the "Update" button in deisgn view on my form and went to the
event procedure to insert your coding. This is the button on my Forms that
appends the data to the "Appended Master" table.`

The SQL starts with :
Private Sub Update_List_Click()

and ends with:
Me.Visible = False

I just inserted your codes in between these 2 lines. I kept getting erors.

I hope I did the right thing. With the updated info I provided to you, could
you let me know what to do now ?

Thanks.

In each form check first whether the row already exists in the table. If not
insert a new row into the table, otherwise update the existing row. You
don't say what data types the columns are so I'll assume Supplier is text and
Qty CAR, Qty NCR, Qty Backlogs and Month are all numbers.

So for the quality form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute


For the materials form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty Backlogs],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty Backlogs] & "," & Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty Backlogs] = " & Me.[Qty Backlogs] & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

If the data types of the columns are not as I've assumed you'll need to
amend the expression assigned to the strCriteria variable , and those for the
SQL statements so that the values are correctly delimited with quotes or not
as appropriate. In the above only the Supplier value is delimited with
quotes, using a pair of quotes characters within each string expression,
which is interpreted by Access as a literal quotes character. Alternatively
you can use a single single-quote character, e.g.

strCriteria = "Supplier = '" & Me.Supplier & "' And " & _
"Month" = & Me.Month

or the Chr(34) function:

strCriteria = "Supplier = " & Chr(34) & Me.Supplier & Chr(34) & " And " & _
"Month" = & Me.Month

Ken Sheridan
Stafford, England

Hi,

My databse is called Supplier Scoreacrd. I have a Master table which
contains the following fileds: Supplier, Qty CAR, Qty NCR, Qty Backlogs,
Month. I then created 2 separate input forms, Quality & Materials.

In the Quality form, I used a Query which brings up Supplier, Qty CAR, Qty
NCR & Month.

In the Materials form, I used a Query which brings up Supplier, Qty Backlogs
and Month.

I set up the database such that the Operator uses the Quality input form if
he is from the Quality departmnet and Materials input if he is from
Materials. In the main Switchboard, the operator clicks either Materials or
Quality. This then takes him to another screen which prompts him to choose a
Month from a drop down.

When the month is chosen, another Form opens with a list of all the
Suppliers and the rest of the fields for that form. The month chosen appears
in each field under the column Month for each Supplier. The person enters the
data for Qty CAR, etc.

When he is finish, he clicks an "Update" button which appends the fields in
the form to the Master table.

The problem I have is since there are 2 different users "Quality &
Materials" and they are both choosing the same Month for entry, when they
click update, the record is being populated twice for the month i.e.

The Master table should reflect the scores as such: Supplier, NCR, CAR,
Backlogs, Month.
If Supplier, NCR, CAR & Month is there from the Quality input, then when
Materials input Backlogs for the same Month, these fields get populated in
another row below with the same month.

How do I get the database to append the info in the same row without
creating duplicate records for the same month?

Anyone have any ideas?

 

[Guest]

You've put the code in the right place, but you'll need to amend my code to
take account of the different column names, the additional column and the
Month column being of text data type.

For the quality form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " & _
"Month = """& Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name]," & _
"[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
"""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute


For the materials form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " & _
"Month = """& Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name],[Qty Backlogs],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty Backlogs] & ",""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty Backlogs] = " & Me.[Qty Backlogs] & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

Incidentally the above is not SQL, its VBA code the SQL is the code of the
append and update queries built within the VBA code. Obviously I cannot test
it, so it might require some debugging at your end.

I'm rather concerned, however, that you have two tables with identical
columns (fields) and are appending data from one to the other. Ina
relational database a fundamental principle is that data are stored once and
once only. Duplication of the same data or the storage of values which are
derived from other values, e.g. a GrossPrice derived from NetPrice and
TaxRate, is known as redundancy and leaves the database open to update
anomalies which would out it in an inconsistent state, e.g. in my example a
GrossPrice value could be changes so that it is no longer NetPrice *
(1+TaxRate) with which it can be computed.

Finally the other respondent's reply has the beauty of simplicity, but the
drawback of being completely useless. I'm afraid that his stated opinion of
his own abilities and his disdain for professional developers is not
supported by the quality of his 'advice'.

Ken Sheridan
Stafford, England

Ken:

You seem to understand what I want and I believe you have the answer for me.
To be honest with you, I have zero SQl experience but I am willing to try
anything. See if this makes sense:

I have a table called [Master Input Table]. I ran 2 queries out of this
table to get either the info I need for the Quality form or the Materials
Form. I named the queries Materials and Quality, respectively. I built the
forms from these queries with the data as I outlined in my 1st email. These
forms will be used to append the data to a table called [Appended Master].

The field names that are in the [Appended Master] Table being populated
through the Quality & Materials forms are: Supplier Code (text), Supplier
Name (text), Qty CAR, Qty NCR, Qty Backlogs, Month (text). It is the same
fields in the [Master Input Table]

I clicked on the "Update" button in deisgn view on my form and went to the
event procedure to insert your coding. This is the button on my Forms that
appends the data to the "Appended Master" table.`

The SQL starts with :
Private Sub Update_List_Click()

and ends with:
Me.Visible = False

I just inserted your codes in between these 2 lines. I kept getting erors.

I hope I did the right thing. With the updated info I provided to you, could
you let me know what to do now ?

Thanks.

In each form check first whether the row already exists in the table. If not
insert a new row into the table, otherwise update the existing row. You
don't say what data types the columns are so I'll assume Supplier is text and
Qty CAR, Qty NCR, Qty Backlogs and Month are all numbers.

So for the quality form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute


For the materials form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty Backlogs],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty Backlogs] & "," & Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty Backlogs] = " & Me.[Qty Backlogs] & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

If the data types of the columns are not as I've assumed you'll need to
amend the expression assigned to the strCriteria variable , and those for the
SQL statements so that the values are correctly delimited with quotes or not
as appropriate. In the above only the Supplier value is delimited with
quotes, using a pair of quotes characters within each string expression,
which is interpreted by Access as a literal quotes character. Alternatively
you can use a single single-quote character, e.g.

strCriteria = "Supplier = '" & Me.Supplier & "' And " & _
"Month" = & Me.Month

or the Chr(34) function:

strCriteria = "Supplier = " & Chr(34) & Me.Supplier & Chr(34) & " And " & _
"Month" = & Me.Month

Ken Sheridan
Stafford, England

Hi,

My databse is called Supplier Scoreacrd. I have a Master table which
contains the following fileds: Supplier, Qty CAR, Qty NCR, Qty Backlogs,
Month. I then created 2 separate input forms, Quality & Materials.

In the Quality form, I used a Query which brings up Supplier, Qty CAR, Qty
NCR & Month.

In the Materials form, I used a Query which brings up Supplier, Qty Backlogs
and Month.

I set up the database such that the Operator uses the Quality input form if
he is from the Quality departmnet and Materials input if he is from
Materials. In the main Switchboard, the operator clicks either Materials or
Quality. This then takes him to another screen which prompts him to choose a
Month from a drop down.

When the month is chosen, another Form opens with a list of all the
Suppliers and the rest of the fields for that form. The month chosen appears
in each field under the column Month for each Supplier. The person enters the
data for Qty CAR, etc.

When he is finish, he clicks an "Update" button which appends the fields in
the form to the Master table.

The problem I have is since there are 2 different users "Quality &
Materials" and they are both choosing the same Month for entry, when they
click update, the record is being populated twice for the month i.e.

The Master table should reflect the scores as such: Supplier, NCR, CAR,
Backlogs, Month.
If Supplier, NCR, CAR & Month is there from the Quality input, then when
Materials input Backlogs for the same Month, these fields get populated in
another row below with the same month.

How do I get the database to append the info in the same row without
creating duplicate records for the same month?

Anyone have any ideas?

 

[Guest]

That was a funny but technically correct joke! I agree that I should not have
to duplicate the table but I did not know of other ways to achieve the same
results due to my lack of expertise in Access. If you don't mind I can
explain what I want in another email and you can suggest a better way but it
is up to you.

So I tried the new code and here is what I have in the event procedure for
Quality Form (I added a comment to the line where I got a syntax error):

Private Sub Update_List_Click()
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

This line below is highlighted as a syntax. The spelling is fine otherwise.

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " &
"Month = """ & Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name]," & _
"[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
"""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

Me.Visible = False

End Sub

It is the same for the Material's form except this line is giving a syntax
error:

If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia)Then

Any thoughts?

You've put the code in the right place, but you'll need to amend my code to
take account of the different column names, the additional column and the
Month column being of text data type.

For the quality form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " & _
"Month = """& Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name]," & _
"[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
"""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute


For the materials form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " & _
"Month = """& Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name],[Qty Backlogs],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty Backlogs] & ",""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty Backlogs] = " & Me.[Qty Backlogs] & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

Incidentally the above is not SQL, its VBA code the SQL is the code of the
append and update queries built within the VBA code. Obviously I cannot test
it, so it might require some debugging at your end.

I'm rather concerned, however, that you have two tables with identical
columns (fields) and are appending data from one to the other. Ina
relational database a fundamental principle is that data are stored once and
once only. Duplication of the same data or the storage of values which are
derived from other values, e.g. a GrossPrice derived from NetPrice and
TaxRate, is known as redundancy and leaves the database open to update
anomalies which would out it in an inconsistent state, e.g. in my example a
GrossPrice value could be changes so that it is no longer NetPrice *
(1+TaxRate) with which it can be computed.

Finally the other respondent's reply has the beauty of simplicity, but the
drawback of being completely useless. I'm afraid that his stated opinion of
his own abilities and his disdain for professional developers is not
supported by the quality of his 'advice'.

Ken Sheridan
Stafford, England

Ken:

You seem to understand what I want and I believe you have the answer for me.
To be honest with you, I have zero SQl experience but I am willing to try
anything. See if this makes sense:

I have a table called [Master Input Table]. I ran 2 queries out of this
table to get either the info I need for the Quality form or the Materials
Form. I named the queries Materials and Quality, respectively. I built the
forms from these queries with the data as I outlined in my 1st email. These
forms will be used to append the data to a table called [Appended Master].

The field names that are in the [Appended Master] Table being populated
through the Quality & Materials forms are: Supplier Code (text), Supplier
Name (text), Qty CAR, Qty NCR, Qty Backlogs, Month (text). It is the same
fields in the [Master Input Table]

I clicked on the "Update" button in deisgn view on my form and went to the
event procedure to insert your coding. This is the button on my Forms that
appends the data to the "Appended Master" table.`

The SQL starts with :
Private Sub Update_List_Click()

and ends with:
Me.Visible = False

I just inserted your codes in between these 2 lines. I kept getting erors.

I hope I did the right thing. With the updated info I provided to you, could
you let me know what to do now ?

Thanks.

In each form check first whether the row already exists in the table. If not
insert a new row into the table, otherwise update the existing row. You
don't say what data types the columns are so I'll assume Supplier is text and
Qty CAR, Qty NCR, Qty Backlogs and Month are all numbers.

So for the quality form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute


For the materials form:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strCriteria = "Supplier = """ & Me.Supplier & """ And " & _
"Month" = & Me.Month

' does row already exist?
If IsNull(DLookup("Supplier","Master",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO Master" & _
"(Supplier,[Qty Backlogs],Month) " & _
"VALUES(""" & Me.Supplier & ""," & _
Me.[Qty Backlogs] & "," & Me.Month
Else
' if so update existing row
strSQL = "UPDATE Master " & _
"SET [Qty Backlogs] = " & Me.[Qty Backlogs] & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

If the data types of the columns are not as I've assumed you'll need to
amend the expression assigned to the strCriteria variable , and those for the
SQL statements so that the values are correctly delimited with quotes or not
as appropriate. In the above only the Supplier value is delimited with
quotes, using a pair of quotes characters within each string expression,
which is interpreted by Access as a literal quotes character. Alternatively
you can use a single single-quote character, e.g.

strCriteria = "Supplier = '" & Me.Supplier & "' And " & _
"Month" = & Me.Month

or the Chr(34) function:

strCriteria = "Supplier = " & Chr(34) & Me.Supplier & Chr(34) & " And " & _
"Month" = & Me.Month

Ken Sheridan
Stafford, England

:

Hi,

My databse is called Supplier Scoreacrd. I have a Master table which
contains the following fileds: Supplier, Qty CAR, Qty NCR, Qty Backlogs,
Month. I then created 2 separate input forms, Quality & Materials.

In the Quality form, I used a Query which brings up Supplier, Qty CAR, Qty
NCR & Month.

In the Materials form, I used a Query which brings up Supplier, Qty Backlogs
and Month.

I set up the database such that the Operator uses the Quality input form if
he is from the Quality departmnet and Materials input if he is from
Materials. In the main Switchboard, the operator clicks either Materials or
Quality. This then takes him to another screen which prompts him to choose a
Month from a drop down.

When the month is chosen, another Form opens with a list of all the
Suppliers and the rest of the fields for that form. The month chosen appears
in each field under the column Month for each Supplier. The person enters the
data for Qty CAR, etc.

When he is finish, he clicks an "Update" button which appends the fields in
the form to the Master table.

The problem I have is since there are 2 different users "Quality &
Materials" and they are both choosing the same Month for entry, when they
click update, the record is being populated twice for the month i.e.

The Master table should reflect the scores as such: Supplier, NCR, CAR,
Backlogs, Month.
If Supplier, NCR, CAR & Month is there from the Quality input, then when
Materials input Backlogs for the same Month, these fields get populated in
another row below with the same month.

How do I get the database to append the info in the same row without
creating duplicate records for the same month?

Anyone have any ideas?

 

[Guest]

I seem to have managed to combine both a typo and a syntax error in the one
line, strCriteria having become strCritertia, and the final closing
parenthesis is missing! It should have been:

If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCriteria)) Then

Ken Sheridan
Stafford, England

That was a funny but technically correct joke! I agree that I should not have
to duplicate the table but I did not know of other ways to achieve the same
results due to my lack of expertise in Access. If you don't mind I can
explain what I want in another email and you can suggest a better way but it
is up to you.

So I tried the new code and here is what I have in the event procedure for
Quality Form (I added a comment to the line where I got a syntax error):

Private Sub Update_List_Click()
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

This line below is highlighted as a syntax. The spelling is fine otherwise.

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " &
"Month = """ & Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name]," & _
"[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
"""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

Me.Visible = False

End Sub

It is the same for the Material's form except this line is giving a syntax
error:

If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia)Then

Any thoughts?

 

[Guest]

Hey Ken:

The line was fixed but all the text under it showing up as red and the code
is still not successful.

Do you mind re-checking the rest of the code to ensure that everthing is
error free?

I am soo close to resolving this yet so far.

I seem to have managed to combine both a typo and a syntax error in the one
line, strCriteria having become strCritertia, and the final closing
parenthesis is missing! It should have been:

If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCriteria)) Then

Ken Sheridan
Stafford, England

That was a funny but technically correct joke! I agree that I should not have
to duplicate the table but I did not know of other ways to achieve the same
results due to my lack of expertise in Access. If you don't mind I can
explain what I want in another email and you can suggest a better way but it
is up to you.

So I tried the new code and here is what I have in the event procedure for
Quality Form (I added a comment to the line where I got a syntax error):

Private Sub Update_List_Click()
Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

This line below is highlighted as a syntax. The spelling is fine otherwise.

strCriteria = "[Supplier Code] = """ & Me.[Supplier Code] & """ And " &
"Month = """ & Me.Month & """"

' does row already exist?
If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia) Then
' if not insert new row
strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name]," & _
"[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty CAR] "," & Me.[Qty NCR] & "," & _
"""" & Me.Month & """"
Else
' if so update existing row
strSQL = "UPDATE [Appended Master] " & _
"SET [Qty CAR] = " & Me.[Qty CAR] & _
", [Qty NCR] = " & Me.[Qty NCR] " & _
"WHERE " & strCriteria
End If

cmd.CommandText = strSQL
cmd.Execute

Me.Visible = False

End Sub

It is the same for the Material's form except this line is giving a syntax
error:

If IsNull(DLookup("[Supplier Code]","[ Appended Master]",strCritertia)Then

Any thoughts?

 

[Guest]

There is a missing ampersand in the expression to build the SQL statement,
between Me.[Qty CAR] and "," & Me.[Qty NCR]. It should be:

strSQL = "INSERT INTO [Appended Master]" & _
"([Supplier Code],[Supplier Name]," & _
"[Qty CAR],[Qty NCR],Month) " & _
"VALUES(""" & Me.[Supplier Code] & ""," & _
""" & Me.[Supplier Name] & ""," & _
Me.[Qty CAR] & "," & Me.[Qty NCR] & "," & _
"""" & Me.Month & """"

Now you can see why debugging is such an important part of programming, and
why most of our working day is spent staring at the screen muttering "Why on
earth is it doing that?"

Ken Sheridan
Stafford, England