Rabu, 06 Juli 2011

RE: [MS_AccessPros] Re: Code Not Working

 

Abdul-

Error 3201 says you're not supplying a value in a foreign key that matches a
value in a related table. What is the structure of tblReceiptLines, and what is
the structure of any table to which it is related with a referential integrity
rule?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Crystal
Sent: Wednesday, July 06, 2011 4:22 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Code Not Working

Hi Abdul,

adding on to what Bill said ... [hi bill :) ]

> "using following code to update subform from another table "

you are actually using a form control to do the update ... is your intention to
get this value from a table?

since the code is behind the form, instead of this:
& [Forms]![Form5]![CustomerCode] &

you can do this:
& Me.CustomerCode &

you should also make sure the control has a value before you use it in your
equation -- or wrap the refernce in NZ (null to zero)

& Nz(Me.CustomerCode,"") &

perhaps the problem is you do not have a reference to a DAO library, which your
code needs. What version of Access are you using?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should always
compile and save before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!) -- then Save

~~~~~ also be sure to use Option Explicit at the top of each module so variables
that are not declared or are misspelled will be picked up

Option Explicit ' require variable declaration

Warm Regards,
Crystal

*
(: have an awesome day :)
*

--- On Wed, 7/6/11, Bill Mosca wrote:

> Abdul
>
> Maybe there is an error in your SQL statement. Put a break
> point on this line:
> Set db = CurrentDb()
>
> Run the code. When it stops open the Immediate window and
> type this"
> ?strSql
>
> Press Enter. This will print out the string in the
> Immediate window so you can see exactly what JET sees. Copy
> the string and try running it in a new query in SQL view.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com,
> Abdul Shakeel <abdul.shakeel@...> wrote:
> >
> > Hi All,
> >  
> > I am using following code to update subform from
> another table but having Run Time Error "3201" can any one
> tell me what I am doing wrong.
> >  
> > Private Sub CustomerCode_AfterUpdate()
> >     Dim db As DAO.Database
> >     Dim strSql As String
> >     Dim rs As DAO.Recordset
> >     Dim rs1 As DAO.Recordset
> >     Dim lngReceiptID As Long
> >    
> >     strSql = "SELECT tblCustomerInvoices.ID,
> tblCustomerInvoices.CustomerCode,
> tblCustomerInvoices.InvoiceDate,
> tblCustomerInvoices.InvoiceNo,
> tblCustomerInvoices.InvoiceAmount,
> tblCustomerInvoices.Allocate, tblCustomerInvoices.Status "
> & vbCrLf & _
> >             "FROM tblCustomerInvoices "
> & vbCrLf & _
> >             "WHERE
> (((tblCustomerInvoices.CustomerCode)=""" &
> [Forms]![Form5]![CustomerCode] & """));"
> >            
> >     Set db = CurrentDb()
> >     Set rs = db.OpenRecordset(strSql,
> dbOpenDynaset)
> >     Set rs1 = db.OpenRecordset("tblReceiptLines",
> dbOpenDynaset)
> >     lngReceiptID = Me.ReceiptID
> >    
> >     Do While Not rs.EOF
> >     rs1.AddNew
> >     rs1!ReceiptID = lngReceiptID
> >     rs1!InvoiceNo = rs!InvoiceNo
> >     rs1!InvoiceDate = rs!InvoiceDate
> >     rs1!InvoiceAmount = rs!InvoiceAmount
> >     rs1.Update
> >     rs.MoveNext
> >     Loop
> >    
> >     Set rs = Nothing
> >    
> > End Sub
> >
> >  
> > Thanks & Regards,
> > --
> > Abdul Shakeel
> >

------------------------------------

Yahoo! Groups Links

__._,_.___
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar