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
> >
Tidak ada komentar:
Posting Komentar