Problem Updating Table

[CasperS]

I could sure use some help! This code will write new records to the table, but when it encounters a record already in the file, the .nomatch statement has a "true" value so that when the .Update statement is run an error is displayed telling me that a duplicate key (SHIPTO and PARTNO) is not allowed. Here's the problem code:

UpdateNewDock:

Rem sSQL2 = "[SHIPTO]='SV_Shipto$' and [PARTNO]='SV_Partno$'"
Rem sSQL2 = "SHIPTO = 'SV_Shipto$' AND PARTNO = 'SV_Partno$'"
Rem SQL2 = "SHIPTO = " & SV_Shipto$ & " AND PARTNO = " & SV_PartNo$
Rem The above stmt adds 1 record to table, then give an error Data Type Mismatch in Criteria Expression
Rem If I let it continue, I see that it correctly yields a "false" from the nomatch statement.

sSQL2 = "SHIPTO='SV_Shipto$' AND PARTNO='SV_PartNo$'"
rstNewDock.FindFirst sSQL2
If rstNewDock.NoMatch Then
With rstNewDock
.AddNew
!SHIPTO = SV_Shipto$
!PARTNO = SV_PartNo$
!MHC = SV_MHC
!DockCode = SV_DockCode
!PCI1 = SV_PCI1
!PCI2 = SV_PCI2
!PCI3 = SV_PCI3
!PCI4 = SV_PCI4
!PCI5 = SV_PCI5
.Update
.Bookmark = rstNewDock.LastModified
End With
Else
With rstNewDock
.Edit
!MHC = SV_MHC
!DockCode = SV_DockCode
!PCI1 = SV_PCI1
!PCI2 = SV_PCI2
!PCI3 = SV_PCI3
!PCI4 = SV_PCI4
!PCI5 = SV_PCI5
.Update
End With
End If

 

[Jack Cowley]

I don't know how or where you are running the code but you should do your field or control validation before you run the code.

Jack

 

[CasperS]

Thanks for responding Jack. This code is run from a form button the purpose of which is simply to update the Dock table with new data -- either to add new Dock info, or update an existing record. Could you be more explicit concerning the need to do field or control validation. I don't know what this means.

With one of the Rem'ed out "sSQL2=" statements when I stop the program and view the contents of sSQL2 I see what I would expect to see: "SHIPTO=<number in text format> AND PARTNO=<number in text format>". When I see this, then the .Nomatch correctly identifies a matched record, but the I get the message "data type mismatch in criteria expression".

With the sSQL2 statement that currently being used when I stop the program and view the contents of sSQL2 I see: "SHIPTO='SV_Shipto$' AND PARTNO='SV_Partno'". I don't see the actual numbers (defined as text) in the SV_<fields>, but when the .Update is run, duplicate key is recognized.

Thanks for your help.

 

[Jack Cowley]

I assume that your current form is unbound and you are using code to update the record rather than having the data bound to the table. Do you have a particular reason for doing it this way rather then binding the form to a table or query?

Try this:

sSQL2 = "SHIPTO = '" & SV_Shipto$ & "" AND PARTNO = '" & SV_PartNo$ & "'"

Below is added spaces so you can see the quote marks. Do not add the spaces between the quotes:

sSQL2 = "SHIPTO = ' " & SV_Shipto$ & ' " AND PARTNO = ' " & SV_PartNo$ & " ' "

I assume the two variable as Text. If PartNo is a number then try this:

sSQL2 = "SHIPTO = ' " & SV_Shipto$ & ' " AND PARTNO = " & SV_PartNo$

hth,
Jack