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
David said:
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.
Ken Sheridan said:
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?