Adding Invalid Client Codes

G

Guest

We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?


'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell
 
M

Marshall Barton

Supe said:
We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?


'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell
Click to expand...


The code is using the value in CliCode whether it was found
in th tbl3 or not. You'll have to look earlier in your
procedure to try to determine what value might be in CliCode
when FindFirst fails to find a match.

You should also think about what value you really want in
the ClientCode field in that case. If it's something simple
like Null, just add:
CliCode = Null
above the code you posted.
 
G

Guest

Marshall,

The only place that CliCode is listed earlier in the code is part a Dim temp
as a String. Code listed below.


Private Sub cmdFinalize_Click()
Dim temp As String, WrhsCode As String, CliCode As String, doubles As
Boolean, i As Integer, brnch As String
Dim tbl1 As dao.Recordset, tbl2 As dao.Recordset, tbl3 As dao.Recordset,
tbl4 As dao.Recordset
Dim cntr As Long




Marshall Barton said:
Supe said:
We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?


'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell
Click to expand...


The code is using the value in CliCode whether it was found
in th tbl3 or not. You'll have to look earlier in your
procedure to try to determine what value might be in CliCode
when FindFirst fails to find a match.

You should also think about what value you really want in
the ClientCode field in that case. If it's something simple
like Null, just add:
CliCode = Null
above the code you posted.
Click to expand...
 
M

Marshall Barton

Then the value in the new records should be an empty string
(generally a poor idea).

The problem is that you are proceeding to add new records
even when no match was found. You need to decide what you
want to happen in that situation.
--
Marsh
MVP [MS Access]

The only place that CliCode is listed earlier in the code is part a Dim temp
as a String. Code listed below.


Private Sub cmdFinalize_Click()
Dim temp As String, WrhsCode As String, CliCode As String, doubles As
Boolean, i As Integer, brnch As String
Dim tbl1 As dao.Recordset, tbl2 As dao.Recordset, tbl3 As dao.Recordset,
tbl4 As dao.Recordset
Dim cntr As Long

Supe said:
We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?

'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell
Click to expand...
Click to expand...

Marshall Barton said:
The code is using the value in CliCode whether it was found
in th tbl3 or not. You'll have to look earlier in your
procedure to try to determine what value might be in CliCode
when FindFirst fails to find a match.

You should also think about what value you really want in
the ClientCode field in that case. If it's something simple
like Null, just add:
CliCode = Null
above the code you posted.
Click to expand...
Click to expand...
 
G

Guest

The best scenario would be that if it did not find a match, then the field
where it would have entered the CLIENT CODE if there were a match, it would
either just be blank or if a text could appear in that field that said NEED
TO ADD or something to that effect. Is that possible without having to redo
his entire code? I've just been doing some trial and error on this code from
reading other entriese from this online site, but nothing has worked yet. As
I mentioned before, I have very little code experience. Thanks.


Marshall Barton said:
Then the value in the new records should be an empty string
(generally a poor idea).

The problem is that you are proceeding to add new records
even when no match was found. You need to decide what you
want to happen in that situation.
--
Marsh
MVP [MS Access]

The only place that CliCode is listed earlier in the code is part a Dim temp
as a String. Code listed below.


Private Sub cmdFinalize_Click()
Dim temp As String, WrhsCode As String, CliCode As String, doubles As
Boolean, i As Integer, brnch As String
Dim tbl1 As dao.Recordset, tbl2 As dao.Recordset, tbl3 As dao.Recordset,
tbl4 As dao.Recordset
Dim cntr As Long

Supe wrote:
We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?

'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell
Click to expand...

Marshall Barton said:
The code is using the value in CliCode whether it was found
in th tbl3 or not. You'll have to look earlier in your
procedure to try to determine what value might be in CliCode
when FindFirst fails to find a match.

You should also think about what value you really want in
the ClientCode field in that case. If it's something simple
like Null, just add:
CliCode = Null
above the code you posted.
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

You can set a default code to be used when there is no match
by just adding one line:

CliCode = "No Match"
If Len(tbl1!UPCCase) = 10 Then
. . .

There is another question about what the Nz function should
be doing when there is a match, but the [CLIENT CODE] field
is Null. Do you still want to use the empty string or
should it be something else?
--
Marsh
MVP [MS Access]

The best scenario would be that if it did not find a match, then the field
where it would have entered the CLIENT CODE if there were a match, it would
either just be blank or if a text could appear in that field that said NEED
TO ADD or something to that effect. Is that possible without having to redo
his entire code? I've just been doing some trial and error on this code from
reading other entriese from this online site, but nothing has worked yet. As
I mentioned before, I have very little code experience. Thanks.


Marshall Barton said:
Then the value in the new records should be an empty string
(generally a poor idea).

The problem is that you are proceeding to add new records
even when no match was found. You need to decide what you
want to happen in that situation.

The only place that CliCode is listed earlier in the code is part a Dim temp
as a String. Code listed below.


Private Sub cmdFinalize_Click()
Dim temp As String, WrhsCode As String, CliCode As String, doubles As
Boolean, i As Integer, brnch As String
Dim tbl1 As dao.Recordset, tbl2 As dao.Recordset, tbl3 As dao.Recordset,
tbl4 As dao.Recordset
Dim cntr As Long


Supe wrote:
We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?

'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell


:
The code is using the value in CliCode whether it was found
in th tbl3 or not. You'll have to look earlier in your
procedure to try to determine what value might be in CliCode
when FindFirst fails to find a match.

You should also think about what value you really want in
the ClientCode field in that case. If it's something simple
like Null, just add:
CliCode = Null
above the code you posted.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Perfect! Thanks.

Marshall Barton said:
You can set a default code to be used when there is no match
by just adding one line:

CliCode = "No Match"
If Len(tbl1!UPCCase) = 10 Then
. . .

There is another question about what the Nz function should
be doing when there is a match, but the [CLIENT CODE] field
is Null. Do you still want to use the empty string or
should it be something else?
--
Marsh
MVP [MS Access]

The best scenario would be that if it did not find a match, then the field
where it would have entered the CLIENT CODE if there were a match, it would
either just be blank or if a text could appear in that field that said NEED
TO ADD or something to that effect. Is that possible without having to redo
his entire code? I've just been doing some trial and error on this code from
reading other entriese from this online site, but nothing has worked yet. As
I mentioned before, I have very little code experience. Thanks.


Marshall Barton said:
Then the value in the new records should be an empty string
(generally a poor idea).

The problem is that you are proceeding to add new records
even when no match was found. You need to decide what you
want to happen in that situation.


Supe wrote:
The only place that CliCode is listed earlier in the code is part a Dim temp
as a String. Code listed below.


Private Sub cmdFinalize_Click()
Dim temp As String, WrhsCode As String, CliCode As String, doubles As
Boolean, i As Integer, brnch As String
Dim tbl1 As dao.Recordset, tbl2 As dao.Recordset, tbl3 As dao.Recordset,
tbl4 As dao.Recordset
Dim cntr As Long


Supe wrote:
We had a person set up the code below that under the GET CLIENT CODE is
getting the first 5 digits from a 10 digit UPC, or the first 6 digits from an
11 digit UPC from tbl1(CliCode). It then looks for a match in tbl3 to get
the CLIENT CODE from this table. It then adds this to tbl4 along with the
other information from tbl1. We've recently noticed that if the 5 or 6
digits code(CliCode) doesn't have a match in tbl3(CLIENT CODE), it is still
giving it a different CLIENT CODE from tbl3 that does not match the 5 or 6
digit code. I know very little about coding. From what I've pasted below,
can you tell why it would be giving a CLIENT CODE to a CliCode that does not
have a match?

'********GET CLIENT CODE********
If Len(tbl1!UPCCase) = 10 Then
temp = Mid(tbl1!UPCCase, 1, 5)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
ElseIf Len(tbl1!UPCCase) = 11 Then
temp = Mid(tbl1!UPCCase, 1, 6)
tbl3.FindFirst "[MAN_UPC] = " & temp & ""
If Not tbl3.NoMatch Then
CliCode = Nz(tbl3![CLIENT CODE])
End If
End If

'********ADD INFORMATION TO TABLE*********
If doubles = True Then
For i = 0 To 1
cntr = cntr + 1
If i = 0 Then
WrhsCode = "SUPRBW"
Else
WrhsCode = "SUPHRM"
End If
tbl4.AddNew
tbl4!ID = cntr
tbl4!ClientCode = CliCode
tbl4!UPCCase = tbl1!UPCCase
tbl4!SVSize = tbl1!SVSize
tbl4!SVDescription = tbl1!SVDescription
tbl4!SVItemCd = tbl1!SVItemCd
tbl4!SVSell = tbl1!SVSell


:
The code is using the value in CliCode whether it was found
in th tbl3 or not. You'll have to look earlier in your
procedure to try to determine what value might be in CliCode
when FindFirst fails to find a match.

You should also think about what value you really want in
the ClientCode field in that case. If it's something simple
like Null, just add:
CliCode = Null
above the code you posted.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FormCode - Converting Type-E UPC to Type-A (barcodes) 2
RowNum using Numbers 2 Roman code 6
VBA Code for converting Type-E UPC to Type-A (barcodes) 1
Passing Values between forms 1
Excellent code now a question of Feasibility 7
Creating UPC Codes... 2
coverting numbers to words 1
pulling data from another sheet 2

Top