adding on again...
I just took a look at what your code is doing. You can accomplish the same thing with something like this:
strSq = "INSERT INTO tblReceiptLines (ReceiptID, InvoiceNo, InvoiceDate, InvoiceAmount) " _
& " SELECT " & Me.ReceiptID _
& ", InvoiceNo, InvoiceDate, InvoiceAmount " _
& " FROM tblCustomerInvoices " _
& " WHERE CustomerCode = '" & Nz(me.CustomerCode,"") & "'"
and then running the SQL. This is called an append query. It will append a record to tblReceiptLines for each invoice in tblCustomerInvoices that meets the criteria (which I question since a customer does not necessarily identify a unique invoice)
BUT!
You should not be repeating InvoiceDate and InvoiceAmount! If tblCustomerInvoices has this field:
InvoiceID, autonumber, PK -- PrimaryKey
then all you need to repeat in other tables is InvoiceID. any informaiton associated with it can be displayed anytime. Repeating data introduces chances for it to be different ... then which one is right?
Alternately, if InvoiceNo is unique in tblCustomerInvoices, then it could be used to link the data.
watch the Learn Access videos 1, 2A, 2B, and 2C here:
http://www.YouTube.com/LearnAccessByCrystal
Warm Regards,
Crystal
*
(: have an awesome day :)
*
--- On Wed, 7/6/11, Crystal wrote:
> 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