Hi Toukey,
Watch the spaces around the #
Change
sno = DLookup("[SeqNo]", "tblAllPols", "[tblAllPols].[Issuedate] = # " & vard & " #" & vno & add2 & add3 & add4 & add8)
To
sno = DLookup("[SeqNo]", "tblAllPols", "[tblAllPols].[Issuedate] = #" & vard & "#" & vno & add2 & add3 & add4 & add8)
Change
uquery = "update tblAllPols set InvDate = # " & varx & " # where [tblAllPols].[SeqNo] = " & sno
To
uquery = "update tblAllPols set InvDate = #" & varx & "# where [tblAllPols].[SeqNo] = " & sno
Use code to check your sno for a value greater than 0 after you use DLookup for example:
sno = DLookup("[SeqNo]", "tblAllPols", "[tblAllPols].[Issuedate] = #" & vard & "#" & vno & add2 & add3 & add4 & add8)
'Check for Null or 0
If Nz(sno, 0) = 0 Then
MsgBox "sno value not found.", vbCritical
End If
x is not Declared or given a value in the code below. Should it be xx?
uquery1 = "update tblAllPols set InvNo = " & x & " where [tblAllPols].[SeqNo] =
" & sno
What is vary, varz, xx, and varp? I assume v means variable? They are not declared in the code you posted.
You need to do Validation in your code for your variables like I showed you with the DLookup. If these variables have no value then your queries will not work.
Another thing you can do to help yourself is Debug.Print your SQL. Right after this code:
uquery = "update tblAllPols set InvDate = # " & varx & " # where [tblAllPols].[SeqNo] = " & sno
uquery1 = "update tblAllPols set InvNo = " & x & " where [tblAllPols].[SeqNo] = " & sno
Add this:
Debug.Print "uquery: " & uquery
Debug.Print "uquery1: " & uquery1
Then you can examine the SQL and paste it in the query editor and see if it works.
Kind Regards,
Patrick Wood
http://accesstips.wordpress.com
--- In MS_Access_Professionals@yahoogroups.com, toukey1 <no_reply@...> wrote:
>
>
> I have a form with a button when pressed would lookup a table for the seqno. The code worked fine until the "where clause" in the code below can now exist in more than one row.
>
> How can I amend the code so that the InvDate and InvNo is populated in the other rows?
>
> This is the condensed version of the code:
>
> Dim uquery As String
> Dim uquery1 As String
> Dim sno As Long
>
> vard = Me!IssueDate
> add2 = " And [tblAllPols].[PolicyNo] = '" & vary & " ' "
> add3 = " And [tblAllPols].[Premium] = " & varz
> add4 = " And [tblAllPols].[ClientNo] = " & xx
> vno = " And [tblAllPols].[InvNo] = 0"
> add8 = " And [tblAllPols].[Tax] = " & varp
>
> sno = DLookup("[SeqNo]", "tblAllPols", "[tblAllPols].[Issuedate] = # " & vard & " #" & vno & add2 & add3 & add4 & add8)
>
> uquery = "update tblAllPols set InvDate = # " & varx & " # where [tblAllPols].[SeqNo] = " & sno
> uquery1 = "update tblAllPols set InvNo = " & x & " where [tblAllPols].[SeqNo] = " & sno
>
> Regards
> Toukey
>
Tidak ada komentar:
Posting Komentar