Khalid,
Are you now back to designing a form rather than a report? You can sum fields from your subform's record source in the form header or footer with an expression like:
=Sum([Your Numeric Field Name])
I would:
- add a TransactionID autonumber field to create a primary key
- change the names fo Text11 and Text13 and Text... to names that make sense
- make up your mind exactly what you want to display on your form
Duane Hookom MVP
MS Access
________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: khalidtanweerburrah@yahoo.com
> Date: Tue, 24 Sep 2013 08:31:47 -0700
> Subject: Re: [MS_AccessPros] RE: Need help on a from and its subform
> (Collection Voucher)
>
>
>
> Duane,
>
> There is the need for "Balance"
> This is a subform (already been created and i am entering data.) Main
> form has only two fields from Employee table, EmployeeID, EmployeeName
> setting default value of EmployeeID to my EmployeeID (visible property
> is false) EmployeeName is just shown for the purpose "Maintained by:
> EmployeeName"
>
> At present i have put three text boxes on Main Form below subform
> Text8 Control Source: =PettyCashsubform.Form!Text11 <==== Total Amount
> Received
> Text9 Control Source: =PettyCashsubform.Form!Text13 <==== Total Expenses
> Text15 Control Source: =[Text8]-[Text9] <==== Balance
>
> My requirement is that Balance to be updated after each entry on
> subform. Well if we make TransactionDate as primary key how would we
> enter more than one transaction on the same date?
>
> Khalid
>
> ________________________________
> From: Duane Hookom <duanehookom@hotmail.com>
> To: Access Professionals Yahoo Group
> <ms_access_professionals@yahoogroups.com>
> Sent: Tuesday, September 24, 2013 4:57 PM
> Subject: RE: [MS_AccessPros] RE: Need help on a from and its subform
> (Collection Voucher)
>
>
> Khalid,
>
> So there is no need for a form with the balance?
>
> I wouldn't do anything until you have:
> - created a primary key in the table
> - defined the order when there multiple transactions on the same date
> - created a form for users to enter the from and to dates
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
>> To: MS_Access_Professionals@yahoogroups.com
>> From: khalidtanweerburrah@yahoo.com
>> Date: Mon, 23 Sep 2013 22:06:36 -0700
>> Subject: Re: [MS_AccessPros] RE: Need help on a from and its subform
>> (Collection Voucher)
>>
>>
>>
>> Hi Hookom-
>> There is no Primary key. Yes there could be multiple transaction on the
>> same date each transaction shall be entered separately. It is just a
>> simple data entry form, i have made a report for it asking to input
>> date range in the query
>> Between [From Date:] And [To Date:]
>>
>> Data entry is simple like below i need Balance to be updated automatically
>>
>> Date------------Details-----------------------
>> Cr.-----------Dr.----------Balance
>> 24/09/2013 Petty cash advance 2,000
>> 2,000
>> 24/09/2013 Mess expenses
>> 500 1,500
>> 24/09/2013 Misc. workshop
>> 700 800
>>
>> User just enters Cr. OR Dr. Balance is updated
>>
>> Regards,
>> Khalid
>> ________________________________
>> From: Duane Hookom <duanehookom@hotmail.com>
>> To: Access Professionals Yahoo Group
>> <ms_access_professionals@yahoogroups.com>
>> Sent: Tuesday, September 24, 2013 12:49 AM
>> Subject: RE: [MS_AccessPros] RE: Need help on a from and its subform
>> (Collection Voucher)
>>
>>
>> Regarding other question:
>> ==============================================
>>> In the meanwhile, can you provide some code for a simple "Cash
>>> Register" form having fields:
>>> TransactionDate
>>> Details
>>> AmountReceived
>>> Expenses
>>> Balance
>> ==============================================
>> Is there a primary key?
>> What do you want to see if there are multiple transactions on the
> same date?
>> Does this form need to be updateable?
>>
>> I expect you could do this with a DSum() in the record source to
>> calculate the Balance however this might be very slow.
>>
>> Duane Hookom MVP
>> MS Access
>>
>> ________________________________
>>> To: MS_Access_Professionals@yahoogroups.com
>>> From: khalidtanweerburrah@yahoo.com
>>> Date: Mon, 23 Sep 2013 10:53:21 -0700
>>> Subject: Re: [MS_AccessPros] RE: Need help on a from and its subform
>>> (Collection Voucher)
>>>
>>>
>>>
>>> Duane Hookom
>>>
>>> Thanks a lot for giving some suggestions, as you were not aware that i
>>> was missing Reports On Open event, which for my case was:
>>>
>>> Private Sub Report_Open(Cancel As Integer)
>>> 'Set public variable to true to indicate that the report is in the
>> Open event
>>> bInReportOpenEvent = True
>>>
>>> 'Open ClientCIN Dialog
>>> DoCmd.OpenForm "ClientCIN Dialog", , , , , acDialog
>>>
>>> 'Cancel Report if User clicked the Cancel Button
>>> If IsLoaded("ClientCIN Dialog") = False Then Cancel = True
>>>
>>> 'Set public variable to false to indicate that the Open event is completed
>>> bInReportOpenEvent = False
>>> End Sub
>>>
>>> Every thing is OK now. Thank you once again. Could you please look for
>>> my other question regarding getting "Balance" in a Petty Cash Register
>>>
>>> Regards,
>>> Khalid
>>>
>>> ________________________________
>>> From: Duane Hookom <duanehookom@hotmail.com>
>>> To: Access Professionals Yahoo Group
>>> <ms_access_professionals@yahoogroups.com>
>>> Sent: Monday, September 23, 2013 6:10 PM
>>> Subject: RE: [MS_AccessPros] RE: Need help on a from and its subform
>>> (Collection Voucher)
>>>
>>>
>>> Placing a reference to a control on a form in the criteria of a query
>>> will not cause the form to open. I would guess that most of us have the
>>> form open first, make selections in controls, and only then open the
>>> report.
>>>
>>> If the form remains open, the control on your report should display the
>>> correct value.
>>>
>>> Duane Hookom MVP
>>> MS Access
>>>
>>> ________________________________
>>>> From: khalidtanweerburrah@yahoo.com
>>>>
>>>> Hi John and all,
>>>>
>>>> Got an immediate problem, i have put Dialog box in a query to run a
>>>> report, for that also made Dialog Form and is working OK.
>>>>
>>>> But for another report, when i open the report i get "Enter Parameter
>>>> Value" box, instead of Dialog box, checked the Record Source of "Client
>>>> CIN Dialog" form, which is:
>>>> Freight Invoice Selective ClientQuery
>>>> its SQL is
>>>> SELECT CollectionVoucher.ClientCIN, CollectionVoucher.ConsignmentNo,
>>>> CollectionVoucher.ClientName, CollectionVoucher.ConsigneeID,
>>>> CollectionVoucher.ConsigneeName, CollectionVoucher.CollectionDate,
>>>> CollectionVoucher.CartonNo, CollectionVoucher.Grade,
>>>> CollectionVoucher.ProductName, CollectionVoucher.ProductQty,
>>>> CollectionVoucher.WeightOfCarton, CollectionVoucher.Rate,
>>>> CollectionVoucher.Amount
>>>> FROM CollectionVoucher
>>>> WHERE (((CollectionVoucher.ClientCIN)=[Forms]![Client CIN
>>>> Dialog]![ClientCIN]))
>>>> ORDER BY CollectionVoucher.ClientCIN, CollectionVoucher.ConsignmentNo,
>>>> CollectionVoucher.CartonNo;
>>>>
>>>> While entering value in "Enter Parameter Value" i get the required
>>>> report open, only on the page header a Text box with Control
>>>> Source: =Forms![Client CIN Dialog]!ClientName gives error: #Name?
>>>>
>>>> Why the form "Client CIN Dialog" does not displays ?
>>>>
>>>> Need help urgently as i have to apply this on many other reports
>>>> NOTE:
>>>> After dealing with it i also require any help for my previous question:
>>>>
>>>> In the meanwhile, can you provide some code for a simple "Cash
>>>> Register" form having fields:
>>>> TransactionDate
>>>> Details
>>>> AmountReceived
>>>> Expenses
>>>> Balance
>>>>
>>>> I need the Balance to be updated, either entry is made in
>>>> AmountReceived or Expenses.
>>>>
>>>> Logically if entry is made in AmountReceived it should Sum Balance of
>>>> previous row/entry, if entry is made in Expenses it should subtract
>>>> from previous rows Balance
>>>>
>>>> Regards,
>>>> Khalid Tanweer
>>>>
>>>> ________________________________
>>>> From: Khalid Tanweer <khalidtanweerburrah@yahoo.com>
>>>> To: "MS_Access_Professionals@yahoogroups.com"
>>>> <MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Saturday, September 14, 2013 12:02 PM
>>>> Subject: Re: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>>
>>>> John-
>>>>
>>>> Thank you for making the concept clear in detail.
>>>>
>>>> It broadened my vision and approach. I will be back after the home work
>>>> you assigned me.
>>>>
>>>> In the meanwhile, can you provide some code for a simple "Cash
>>>> Register" form having fields:
>>>> TransactionDate
>>>> Details
>>>> AmountReceived
>>>> Expenses
>>>> Balance
>>>>
>>>> I need the Balance to be updated, either entry is made in
>>>> AmountReceived or Expenses.
>>>>
>>>> Logically if entry is made in AmountReceived it should Sum Balance of
>>>> previous row/entry, if entry is made in Expenses it should subtract
>>>> from previous rows Balance
>>>>
>>>> Khalid
>>>>
>>>> ________________________________
>>>> From: John Viescas <JohnV@msn.com>
>>>> To: MS_Access_Professionals@yahoogroups.com
>>>> Sent: Saturday, September 14, 2013 11:02 AM
>>>> Subject: RE: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>>
>>>> Khalid-
>>>>
>>>> Actually, I suggest you pull out a pencil and paper and lay out your
>>>> tables. Write the name of one "subject" (table) in the upper left
>>>> corner and list all the "attributes" (fields) that describe only that
>>>> subject underneath it. Write a "P" next to the field or fields that
>>>> will be unique for each instance of that subject - those will be
>>>> candidates to become the Primary Key. Write the name of a subject
>>>> that's related to the first subject to the right and list all its
>>>> attributes underneath. As you are listing attributes, be careful to
>>>> include ONLY items that help further describe the subject. Now decide
>>>> how the two subjects are related. If one of the first subject can have
>>>> many related records of the second subject, draw an arrow from left to
>>>> right. If one of the second subject can have many related records of
>>>> the first subject, draw an arrow from right to left. If you have just
>>>> one arrow, you have a one - to - many relationship. To build the
>>>> relationship, you will need a copy of the Primary Key of the "one"
>>>> subject in the table of the "many" subject. If you have arrows in both
>>>> directions, you will need another table in the middle - what I call a
>>>> "linking" table to hold the Primary Key of both tables to model the
>>>> many - to - many relationship you discovered.
>>>>
>>>> Repeat with additional tables until you have it all laid out.
>>>>
>>>> Now go to your database and build what you wrote on paper. If you can
>>>> simply modify existing tables, it's OK to do that.
>>>>
>>>> After you have all your tables built, go to the Relationships window,
>>>> add all your tables, and use the window to define the relationships
>>>> that you drew out on paper. Be sure to specify "enforce referential
>>>> integrity" so that Access can help you keep your data clean. When you
>>>> have finished, post another picture of your Relationships Window to the
>>>> Assistance Needed folder.
>>>>
>>>> Good luck!
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From: MS_Access_Professionals@yahoogroups.com
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
>>>> khalidtanweerburrah@yahoo.com
>>>> Sent: Saturday, September 14, 2013 7:04 AM
>>>> To: MS_Access_Professionals@yahoogroups.com
>>>> Subject: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>>
>>>>
>>>>
>>>> John-
>>>>
>>>> You got it what i meant.
>>>>
>>>> Are you suggesting to create new tables OR modify the existing tables?
>>>>
>>>> I already have tables:
>>>>
>>>> Clients
>>>> Consignment Number
>>>> CollectionVoucher
>>>>
>>>> I am unable to understand following,like ClientCIN (P) perhaps its
>>>> something new for me, could you please describe it,what does within
>>>> brackets P means
>>>>
>>>> Clients: ClientCIN (P), ClientName, etc.
>>>>
>>>> Consignments: ConsignmentID (P), ConsignmentDate, etc.
>>>>
>>>> ClientConsignments: ClientCIN (P1), ConsignmentID (P2), .. other fields
>>>>
>>>> Khalid
>>>>
>>>>
>>>> --- In
>>>>
>>>
>>
> ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>,
>>>> <JohnV@...<mailto:JohnV@...>> wrote:
>>>> Khalid-
>>>>
>>>> Reading your previous reply (which I haven't seen yet from the
>>>> website), you need to establish a many-many relationship between
>>>> Clients and Consignments. You say a Client can have many Consignments,
>>>> and a Consignment may involve many Clients. To do that, you need a
>>>> "linking" table:
>>>>
>>>> Clients: ClientCIN (P), ClientName, etc.
>>>>
>>>> Consignments: ConsignmentID (P), ConsignmentDate, etc.
>>>>
>>>> ClientConsignments: ClientCIN (P1), ConsignmentID (P2), .. other fields
>>>>
>>>> You need to fix your table design before I can help you further.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Thursday, September 12, 2013 8:32 PM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>>
>>>>
>>>> John-
>>>>
>>>> Your all suggestions i have noted and shall implement in tables.
>>>>
>>>> What i was doing extra and duplicate entries in sub form for fields
>>>> "ClientCIN" and "ClientName" i shifted these two fields from detail
>>>> section to form header, where you told me to put Unbound cmbClient. On
>>>> Before update event of sub form you provided me code:
>>>> ' If CollectionDate is empty,
>>>> If IsNull(Me.CollectionDate) Then
>>>> ' Copy it from the parent form
>>>> Me.CollectionDate = Me.Parent!CollectionDate
>>>> End If
>>>> And same like for Grade.
>>>>
>>>> I used your logic and added the same pattern for "ClientCIN" and
>>>> "ClientName" while the filter is ON on cmbClient
>>>>
>>>> in brief my table is updating and i am getting correct data. I would
>>>> like to thank you again because you helped all the way and provided
>>>> necessary help.
>>>>
>>>> Now moving ahead on the same sub form to make it error free, while
>>>> entering "CartonNo" for a selected Client i need to stop entering
>>>> duplicate CartonNo
>>>> Supposing CartonNo "004" this CartonNo might have been entered for some
>>>> other Clients also for the same ConsignmentNo
>>>>
>>>> How to manage it?
>>>>
>>>> Khalid
>>>>
>>>> ________________________________
>>>> From: "khalidtanweerburrah@...<mailto:khalidtanweerburrah@...>"
>>>> <khalidtanweerburrah@...<mailto:khalidtanweerburrah@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Wednesday, September 11, 2013 9:18 PM
>>>> Subject: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>>
>>>> John-
>>>>
>>>> Read your instructions and advise carefully, the question you asked and
>>>> answered yourself (if so?) .
>>>>
>>>> Yes that is the case one client have many consignments and on the same
>>>> time many of other clients are also involved in those consignments.
>>>>
>>>> Actually physically we schedule a Consignment after some period, then
>>>> we inform our clients through mobile phones and skype that Consignment
>>>> for Grade A or Grade B, or both (with a unique Consignment Number) are
>>>> to be collected on a supposed date. Our team goes to the clients in
>>>> different cities and collect the consignment and prepare / get packing
>>>> list and weight, quantity of each Carton. Then we to enter the details
>>>> in Form "Collection Voucher". Hope you would be now getting the idea.
>>>>
>>>> Now i will first make a backup of my db then then work on its copy as
>>>> per your suggestions and see the results. I will be back hopefully with
>>>> some achievement or if i have still some problems i will inform you.
>>>>
>>>> Thanks for your reply.
>>>>
>>>> Khalid
>>>>
>>>>
>>>> --- In
>>>>
>>>
>>
> ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>,
>>>> <JohnV@...<mailto:JohnV@...>> wrote:
>>>> Khalid-
>>>>
>>>> EVERY table should have a Primary Key. Most of your tables do not
>> have one.
>>>>
>>>> Why is there a ConsignmentNo field in Clients? Can a Client have many
>>>> Consignments? If so, there should be no ConsignmentNo field in
>>>> Clients. Instead, you need something like ClientCIN (the unique key
>>>> for clients?) in your Consignments table. That will tell you which
>>>> client owns which Consignment.
>>>>
>>>> You need to define Primary Keys in all your tables, then use those keys
>>>> to define the relationships. You will never get what you have now to
>>>> work.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
>>>> khalidtanweerburrah@...
>>>> Sent: Tuesday, September 10, 2013 7:04 AM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: RE: RE: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> John-
>>>>
>>>> I have uploaded two files in the 2_AssistantNeeded folder
>>>>
>>>> PCTL-Relationships
>>>> PCTL-Tables
>>>>
>>>> for your review, hope it suffice
>>>>
>>>> Regards,
>>>> Khalid
>>>>
>>>>
>>>> --- In
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
>>>>
>>>
>>
> <ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>>
>>>> wrote:
>>>> Khalid-
>>>>
>>>> It would help to know the design of your tables and the relationships
>>>> between them. Perhaps do a screen grab of your Relationships window
>>>> and upload that to Files / 2_Assitance Needed.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Monday, September 09, 2013 11:06 AM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> John-
>>>>
>>>> You are right, Client Name should be in only one table. Yes i need the
>>>> client name with different shipments and also in one shipment for same
>>>> ClientCIN for many entries starting from CartonNo = 001 to many (may be
>>>> less than 10 or more than 100). I need ClientCIN and ClientName should
>>>> be appended in tbl.Collection Voucher as i have already made 3-4
>>>> reports on different queries with tbl.Collection Voucher.
>>>>
>>>> For example see that in Report "Collection Voucher All Clients" its
>>>> Record Source is "CollectionVoucherAllClientsQuery" its SQL is:
>>>>
>>>> SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN,
>>>> CollectionVoucher.ClientName, CollectionVoucher.CollectionDate,
>>>> CollectionVoucher.CartonNo, CollectionVoucher.Grade,
>>>> CollectionVoucher.ProductName, CollectionVoucher.ProductQty,
>>>> CollectionVoucher.WeightOfCarton, CollectionVoucher.Rate,
>>>> CollectionVoucher.Amount
>>>> FROM CollectionVoucher
>>>> WHERE (((CollectionVoucher.ConsignmentNo) Like [Enter Consignment No]))
>>>> ORDER BY CollectionVoucher.CartonNo;
>>>>
>>>> Regards,
>>>> Khalid
>>>>
>>>> ________________________________
>>>> From: John Viescas <JohnV@...<mailto:JohnV@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Friday, September 6, 2013 7:03 PM
>>>> Subject: RE: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> Khalid-
>>>>
>>>> But that doesn't explain why you are copying the client name. Client
>>>> Name should be in only one table. If you need the client name with
>>>> different shipments, use a query that joins the client table on
>>>> ClientCIN. You don't need anything more than the linking field.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Friday, September 06, 2013 3:27 PM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>>
>>>> John-
>>>>
>>>> Only because on the sub form user has to enter consignment details of
>>>> many clients and each client dispatches different quantity of Cartons
>>>> containing different Products, these number of Cartons may be more than
>>>> 100 and user has to enter one by one details of each carton.
>>>>
>>>> That is why i was asking that user may not by mistake enter duplicate
>>>> Carton Number.
>>>>
>>>> Regards,
>>>> Khalid
>>>> ________________________________
>>>> From: John Viescas <JohnV@...<mailto:JohnV@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Thursday, September 5, 2013 10:24 PM
>>>> Subject: RE: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> Khalid-
>>>>
>>>> I do not understand why you are doing that.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Thursday, September 05, 2013 4:39 PM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> John-
>>>>
>>>> But on the sub form after selecting ClientCIN from unbouond cmbClient
>>>>
>>>> On the detail section i have the field ClientCIN where user selects
>>>> ClientCIN and then ClientName is populated in ClientName field,
>>>> ClientName has Control Source: =ClientCIN.Column(1)
>>>>
>>>> ClientCIN has After Update event:
>>>> Private Sub ClientCIN_AfterUpdate()
>>>> ClientName.Value = CmbClientName
>>>> End Sub
>>>>
>>>> So in this situation what do you say?
>>>>
>>>> regards,
>>>> Khalid
>>>>
>>>> ________________________________
>>>> From: John Viescas <JohnV@...<mailto:JohnV@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Thursday, September 5, 2013 7:14 PM
>>>> Subject: RE: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> Khalid-
>>>>
>>>> Why not simply put an index on ClientCIN and CartonNo in the underlying
>>>> table and mark it No Duplicates?
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>>
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Thursday, September 05, 2013 9:43 AM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> John-
>>>>
>>>> Thank you very much. It worked, this means that an expression works
>>>> within the form. Before your reply i struggled in Before Update event
>>>> but was unable to put correct code. Thanks again!
>>>>
>>>> Now i have put these two fields on the Form Header and set their
>>>> Visible property set to "No", because they are already displaying on
>>>> the main form. Our aim was to update data in table for these two fields
>>>> and also on the sub form these two fields remain blank, till the Before
>>>> Update event triggers ?
>>>>
>>>> Now i have two more issues to resolve, i will mention one here so that
>>>> we should resolve it first and move to the other.
>>>>
>>>> On the sub form user enters the marked number of Carton in "CartonNo"
>>>> how could i rid off from entering duplicate entry and displaying msgbox
>>>> for it for the client whose consignment details we are entering.
>>>> Supposing for ClientCIN = 1 we have entered "CartonNo"
>>>> 1
>>>> 2
>>>> 3 and so on
>>>>
>>>> And for CleintCIN=2 we go on entering Carton numbers
>>>> 1
>>>> 2
>>>> 3 and now user by mistake enters 3 again, with a msgbox or some other
>>>> way user should be warned that its a "duplicate entry for ClientCIN = 2
>>>> and Carton No 3".
>>>> (and also the filter is ON for unbound cmbClient on the form header)
>>>> On some other forms i am using the following code on After Update
>>>> event, but its for check of single field. Here i want check for two
>>>> fields:
>>>> ClientCIN or ClientName and CartonNo
>>>> I am pasting the code:
>>>>
>>>> Private Sub CompanyDesignation_AfterUpdate()
>>>> Text7 = Text9 <------- Visible property for both text boxes is set to
>>>> "No", Text7 Control Source is "Number"
>>>> <------- and Text9 Control Source is:
>>>> =[CompanyDesignation]
>>>> Dim SID As String
>>>> Dim stLinkCriteria As String
>>>> Dim rsc As DAO.Recordset
>>>> Set rsc = Me.RecordsetClone
>>>> SID = Me.Text7.Value
>>>> stLinkCriteria = "[Number]=" & "'" & SID & "'"
>>>> If DCount("Number", "MainCompanyDesignation", stLinkCriteria)> 0 Then
>>>> Me.Undo
>>>> MsgBox "Company Designation: '" _
>>>> & SID & "' has already been allotted. You will now " _
>>>> & vbCr & vbCr & "been taken to the record of Company Designation. " _
>>>> & SID & ".", vbInformation _
>>>> , "PCTL - Duplicate Entry"
>>>> rsc.FindFirst stLinkCriteria
>>>> Me.Bookmark = rsc.Bookmark
>>>> End If
>>>> Set rsc = Nothing
>>>> CompanyDesignation.SetFocus
>>>> End Sub
>>>> ----------
>>>> Hope you would do the favor and guidance as usual.
>>>> Regards,
>>>> Khalid
>>>>
>>>> ________________________________
>>>> From: John Viescas <JohnV@...<mailto:JohnV@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Wednesday, September 4, 2013 10:46 PM
>>>> Subject: RE: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>>
>>>> Khalid-
>>>>
>>>> You could do it in the BeforeUpdate event of the subform. The two
>>>> fields need to have a Control Source of the fields in the table you
>>>> want to update.
>>>>
>>>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>>> ' If CollectionDate is empty,
>>>> If IsNull(Me.CollectionDate) Then
>>>> ' Copy it from the parent form
>>>> Me.COllectionDate = Me.Parent!CollectionDate
>>>> End If
>>>> ' If Grade is empty,
>>>> If IsNull(Me.Grade) Then
>>>> ' Copy it from the outer form
>>>> Me.Grade = Me.Parent!Grade
>>>> End If
>>>> End Sub
>>>>
>>>> But I wonder why you are duplicating the values in two tables. Good
>>>> relational table design dictates that a value is stored only once.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Wednesday, September 04, 2013 7:31 PM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>> John-
>>>> I a m blind about what code to be written. Could you please give it ?
>>>>
>>>> ________________________________________
>>>> From: John Viescas <JohnV@...<mailto:JohnV@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Wednesday, September 4, 2013 10:04 PM
>>>> Subject: RE: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>> Khalid-
>>>>
>>>> Setting the Control Source to an expression does NOT update the field -
>>>> it only affects what is displayed. If you want the CollectionDate and
>>>> Grade to be copied from other fields, you need to write code to do
>>>> that.
>>>>
>>>> John Viescas, Author
>>>> Microsoft Access 2010 Inside Out
>>>> Microsoft Access 2007 Inside Out
>>>> Microsoft Access 2003 Inside Out
>>>> Building Microsoft Access Applications
>>>> SQL Queries for Mere Mortals
>>>> http://www.viescas.com/
>>>> (Paris, France)
>>>>
>>>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Khalid
>>>> Tanweer
>>>> Sent: Wednesday, September 04, 2013 5:57 PM
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Subject: Re: [MS_AccessPros] RE: RE: Need help on a from and its
>>>> subform (Collection Voucher)
>>>>
>>>> Hi Bill-
>>>>
>>>> No Bill about my present problem. Let me brief it to you to get the
>>> scenario.
>>>>
>>>> I have a form "Collection Voucher" its Record Source is tbl.
>>>> Consignment Number. On the main form there are three Text boxes:
>>>> "ConsignmentNo", "CollectionDate", "Grade" <--------- All OK upto here.
>>>>
>>>> Then there is a Continuous sub form on the main form whose Record
>>>> Source is: SELECT CollectionVoucher.ConsignmentNo,
>>>> CollectionVoucher.ClientCIN, CollectionVoucher.ClientName,
>>>> CollectionVoucher.CollectionDate, CollectionVoucher.CartonNo,
>>>> CollectionVoucher.Number, CollectionVoucher.Grade,
>>>> CollectionVoucher.ProductName, CollectionVoucher.ProductQty,
>>>> CollectionVoucher.WeightOfCarton, CollectionVoucher.Rate,
>>>> CollectionVoucher.Amount FROM CollectionVoucher;
>>>>
>>>> On the sub form header the is unbound "cmbClient" having After Update
>>> Event:
>>>> Private Sub cmbClient_AfterUpdate()
>>>>
>>>> If Not IsNull(Me.cmbClient) Then
>>>> ' Apply a filter
>>>> Me.Filter = "ClientCIN = " & Me.cmbClient
>>>> Me.FilterOn = True
>>>> End If
>>>> DoCmd.GoToRecord , , acLast
>>>> End Sub
>>>>
>>>> Problem is here for two fields on the sub form "CollectionDate" and
>>>> "Grade" are displayed but they are not updating in the table, other
>>>> fields are updating correctly.
>>>> Control Source of "CollectionDate" is: =[Forms]![Collection
>>>> Voucher]![CollectionDate]
>>>> Control Source of "Grade" is: =Forms![Collection Voucher]!Grade
>>>>
>>>> This is why in the Report related to tbl.Collection Voucher with a
>>>> query. "CollectionDate" and "Grade" are blank.
>>>>
>>>> Hope to somehow i'm able to describe my problem. Your suggestions and
>>>> help would be highly appreciated.
>>>>
>>>> Regards
>>>> Khalid
>>>>
>>>> ________________________________________
>>>> From: "wrmosca@...<mailto:wrmosca@...>" <wrmosca@...<mailto:wrmosca@...>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> Sent: Wednesday, September 4, 2013 7:06 PM
>>>> Subject: [MS_AccessPros] RE: RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>> Khalid
>>>>
>>>> I'm coming into this thread rather late so I may not understand all
>>>> that has transpired, but as to your question concerning selecting the
>>>> ClientCIN when you select another ClientCIN the AfterUpdate event will
>>>> fire again thus changing the filter to the selected number.
>>>>
>>>> Does that answer your Question?
>>>>
>>>> Regards,
>>>> Bill Mosca, Founder - MS_Access_Professionals
>>>> http://www.thatlldoit.com<http://www.thatlldoit.com/>
>>>> Microsoft Office Access MVP
>>>>
>>>
>>
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
>>>> My nothing-to-do-with-Access blog
>>>> http://wrmosca.wordpress.com<http://wrmosca.wordpress.com/>
>>>>
>>>> --- In
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
>>>>
>>>
>>
> <ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>>
>>>> wrote:
>>>> John-
>>>> I am still waiting for your reply as our collection team is arriving
>>>> today and my boss is keenly ambitious that we should start our database
>>>> and put data entries particularly we are focused on Collection Voucher
>>>> to obtain results in Reports. I have already made reports on the basis
>>>> of Collection Voucher, but it is missing "CollectionDate" and "Grade".
>>>> You may be away for your some reasons, i again request when you are
>>>> back kindly reply me. I also need help on two more issues, which i will
>>>> discuss after rectifying the current problems.
>>>> (wrmosca, if you get a sight on this message in the absence of John, if
>>>> you could help me i would really appreciate it, because i am running
>>>> out of time.) Regards, Khalid
>>>> --- In
>>>>
>>>
>>
> ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>,
>>>> <khalidtanweerburrah@...<mailto:khalidtanweerburrah@...>> wrote:
>>>>
>>>> John -
>>>>
>>>> Thanks a lot for doing the help and your kind guidance. Well before i
>>>> was not understanding the logic and usage of this filter, now what i
>>>> have understood and tested is that when i select ClintCIN = 1 on the
>>>> unbound cmbClient on the form header it displays records of ClientCIN
>>>> =1 in the detail section and user may enter more records for ClientCIN
>>>> =1 and when wishes change the value in cmbClient.
>>>>
>>>> But while i have selected ClientCIN =1 on the cmbClient and in the
>>>> detail section if i select ClientCIN = 2 it accepts it. Is this OK?
>>>>
>>>> Secondly before when the sub form's Default View was "Datasheet" (now
>>>> its Continuous Form) fields "CollectionDate" and "Grade were updating
>>>> in table, but now they are not, although on the sub form they are
>>>> shown.
>>>>
>>>> I have edited my Subscription to Individual mail setting. Hope now in
>>>> future i would receive your reply in my mail box.
>>>>
>>>> Regards, Khalid
>>>>
>>>> --- In
>>>>
>>>
>>
> ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>
>>>> , <JohnV@...<mailto:JohnV@...>> wrote:
>>>>
>>>> Khalid-
>>>>
>>>> To find the Link properties, open the form in Design view, open the
>>>> Properties window, then select the subform control.
>>>>
>>>> To apply a filter:
>>>>
>>>> Private Sub cmbClient_AfterUpdate()
>>>>
>>>> ' If a value selected
>>>>
>>>> If Not IsNull(Me.cmbClient) Then
>>>>
>>>> ' Apply a filter
>>>>
>>>> Me.Filter = "ClientCN = " & Me.cmbClient
>>>>
>>>> Me.FilterOn = True
>>>>
>>>> End IF
>>>>
>>>> End Sub
>>>>
>>>> I would leave the record selector and navigation buttons.
>>>>
>>>> To change your settings, go to:
>>>>
>>>>
>>>
>>
> http://groups.yahoo.com/neo/groups/MS_Access_Professionals/management/membership
>>>>
>>>> Click the little pencil icon next to Subscription to edit your message
>>>> preference.
>>>>
>>>> John Viescas, Author
>>>>
>>>> Microsoft Access 2010 Inside Out
>>>>
>>>> Microsoft Access 2007 Inside Out
>>>>
>>>> Microsoft Access 2003 Inside Out
>>>>
>>>> Building Microsoft Access Applications
>>>>
>>>> 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>
>>>> [mailto:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> ] On Behalf Of khalidtanweerburrah@...
>>>>
>>>> Sent: Monday, September 02, 2013 2:23 PM
>>>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>> Subject: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>> John-
>>>>
>>>> Reference your first question Why did you lock the three fields in the
>>>> main form? I was assuming and thinking that user may by mistake or for
>>>> an experiment do not alter data there now i have changed their locked
>>>> property to "No "
>>>>
>>>> Your second paragraph, you might laugh at my answer i often can't see
>>>> the "Link Master and Link Child properties of the subform control "
>>>> sometimes by hit and trial i see them. What is the correct way to view
>>>> them?
>>>>
>>>> Thirdly, i have made the subform a Continuous Form. In the header of
>>>> sub form placed an unbound combo box with a Row Source of the Clients
>>>> table:
>>>>
>>>> SELECT Clients.ClientCIN, Clients.ClientName FROM Clients;
>>>>
>>>> for applying a filter to the form to display only the records for that
>>>> consignment that match the selected ClientCIN. I need your help what
>>>> would be the filter or how it is written then i could move further.
>>>>
>>>> As you suggested i changed ClientCIN to Integer.
>>>>
>>>> Should the Record Selector and Navigation Buttons property be set to
>>>> "Yes " of the sub form? OR I put a Command Button asking user "Do you
>>>> want to enter more records ? " At the moment i have put on the "On Open
>>>> " Event of sub form: DoCmd.GoToRecord , , acNewRec
>>>>
>>>> (P.S. I am unable to find settings in the group, where are they located?
>>>>
>>>> regards and Thanks Khalid
>>>>
>>>> --- In
>>>>
>>>
>>
> ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>
>>>> , <JohnV@...<mailto:JohnV@...>> wrote:
>>>>
>>>> Khalid-
>>>>
>>>> For a field to be updatable via a form, the Control Source must be the
>>>> field, not an expression, and the control should not be locked. Why did
>>>> you lock the three fields in the main form?
>>>>
>>>> I assume the Link Master and Link Child properties of the subform
>>>> control are set to ConsignmentNo. That will filter the records in the
>>>> subform to display only the records that match the ConsignmentNo in the
>>>> outer form.
>>>>
>>>> To additionally filter by ClientCIN, I suggest you make the subform a
>>>> Continuous Form, not a Datasheet. In the header of that form, place an
>>>> unbound combo box with a Row Source of the Clients table. In
>>>> AfterUpdate of that control, apply a filter to the form to display only
>>>> the records for that consignment that match the selected ClientCIN.
>>>>
>>>> By the way, I noticed earlier that you said ClientCIN is a Double. Why?
>>>> I would expect it to be Integer or Long Integer. I can't imagine you
>>>> need a fractional value for an ID field.
>>>>
>>>> John Viescas, Author
>>>>
>>>> Microsoft Access 2010 Inside Out
>>>>
>>>> Microsoft Access 2007 Inside Out
>>>>
>>>> Microsoft Access 2003 Inside Out
>>>>
>>>> Building Microsoft Access Applications
>>>>
>>>> SQL Queries for Mere Mortals
>>>>
>>>> http://www.viescas.com/
>>>>
>>>> (Paris, France)
>>>>
>>>> P.S. To get all messages via email, you have to change your settings in
>>>> the Yahoo group.
>>>>
>>>> -----Original Message-----
>>>>
>>>> John-
>>>>
>>>> Thanks for giving the sample code, and sorry for delay in replying it
>>>> was my weekend.
>>>>
>>>> I have made some changes and now working on it almost its working right.
>>>>
>>>> First of all is that Record source of main form is now
>>>> "ConsignmentNumber ". (When New Consignment is scheduled user enters in
>>>> the form bound to tbl.ConsignmentNumber. Fields "ConsignmentNo ",
>>>> "CollectionDate ", "Grade ") Main form has these 3 above fields, and
>>>> their Locked property is Yes. Navigation Buttons property is Yes.
>>>>
>>>> I have put subform (so that user may see the previous records entered
>>>> in the sub form at one place) whose Record Source is: SELECT
>>>> CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN,
>>>> CollectionVoucher.ClientName, CollectionVoucher.CartonNo,
>>>> CollectionVoucher.Number, CollectionVoucher.Grade,
>>>> CollectionVoucher.ProductName, CollectionVoucher.NumberPN,
>>>> CollectionVoucher.ProductQty, CollectionVoucher.WeightOfCarton,
>>>> CollectionVoucher.Rate, CollectionVoucher.Amount FROM
>>>> CollectionVoucher;
>>>>
>>>> Row Source of ClientCIN is: SELECT Clients.ClientCIN,
>>>> Clients.ClientName FROM Clients ORDER BY Clients.ClientCIN;
>>>>
>>>> Now what i want is that on the sub form for a new record only records
>>>> of selected Client should appear which are previously entered for that
>>>> client, so that user do not see records of other clients.
>>>>
>>>> Secondly data for two fields "CollectionDate " and "Grade " is not
>>>> updating in the table. I have put them on sub form and also tried to
>>>> put them on form footer of sub form, Control Source of "CollectionDate
>>>> " is:=[Forms]![Collection Voucher]![CollectionDate]
>>>>
>>>> Hope you would do the favor as usual. Thanking you in advance., regards
>>>> Khalid
>>>>
>>>> -----Original Message-----
>>>>
>>>> Khalid-
>>>>
>>>> Consider what you want to have happen when the user selects either a
>>>> ConsignmentNo or ClientCN in the unbound combo boxes. You could write
>>>> code to either move to the first record that matches the selected
>>>> ConsignmentNo or filter so that only records for that ConsignmentNo
>>>> appear. Here's some sample code for applying a filter.
>>>>
>>>> Private Sub cmbConsignment_AfterUpdate() Dim strFilter As String
>>>>
>>>> ' Build a filter on the selected Consignment
>>>>
>>>> strFilter = "ConsignmentNo = ' " & Me.cmbConsignment & "' "
>>>>
>>>> ' See if a Client also selected
>>>>
>>>> If Not IsNull(Me.cmbClient) Then
>>>>
>>>> ' Add to the filter for Client
>>>>
>>>> strFilter = strFilter & " AND ClientCN = " & Me.cmbClient
>>>>
>>>> End If
>>>>
>>>> ' Apply the filter
>>>>
>>>> Me.Filter = strFilter
>>>>
>>>> Me.FilterOn = True
>>>>
>>>> ' See if no records found (will be on a new row)
>>>>
>>>> If Me.NewRecord Then
>>>>
>>>> ' Ask the user
>>>>
>>>> If vbYes = MsgBox( "No records match the Consignment and Client " & _
>>>>
>>>> "you selected. Do you want to add a new record? ", _
>>>>
>>>> vbQuestion + vbYesNo) Then
>>>>
>>>> ' Copy the values to the bound fields
>>>>
>>>> Me.ConsignmentNo = Me.cmbConsignment
>>>>
>>>> Me.ClientCN = Me.cmbClient
>>>>
>>>> Else
>>>>
>>>> ' Clear the two combos
>>>>
>>>> Me.cmbConsignment = Null
>>>>
>>>> Me.cmbClient = Null
>>>>
>>>> ' Remove the filter
>>>>
>>>> Me.Filter = " "
>>>>
>>>> Me.FilterOn = False
>>>>
>>>> End If
>>>>
>>>> End If
>>>>
>>>> End Sub
>>>>
>>>> You would put similar code in the AfterUpdate event of the Client combo
>>>> box. Note that I have used cmbConsignment as the name of the unbound
>>>> combo box for Consignment and cmbClient as the name of the unbound
>>>> combo box for Client.
>>>>
>>>> John Viescas, Author
>>>>
>>>> Microsoft Access 2010 Inside Out
>>>>
>>>> Microsoft Access 2007 Inside Out
>>>>
>>>> Microsoft Access 2003 Inside Out
>>>>
>>>> Building Microsoft Access Applications
>>>>
>>>> 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>
>>>> [mailto:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> ] On Behalf Of khalidtanweerburrah@...
>>>>
>>>> Sent: Friday, August 30, 2013 7:50 PM
>>>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>> Subject: [MS_AccessPros] RE: Need help on a from and its subform
>>>> (Collection Voucher)
>>>>
>>>> John, Thanks for diverting me to the right approach, i am working upon
>>>> it and checking the results. In the meanwhile please suggest that
>>>> having ConsignmentNo and CIN unbound fields, if they they Row Source:
>>>> SELECT [Consignment Number].ConsignmentNo, [Consignment
>>>> Number].CollectionDate, [Consignment Number].Grade FROM [Consignment
>>>> Number] ORDER BY [Consignment Number].ConsignmentNo; and same as for
>>>> CIN, is it a right approach or wrong? Secondly after input in the last
>>>> field "Amount " on the form how the user should be prompted if he wants
>>>> to enter more records with the same ConsignmentNo and CIN OR same
>>>> ConsignmentNo but other CIN Presently i am trying only for
>>>> ConsignmentNo with On Got Focus event: Private Sub Amount_GotFocus()
>>>> Dim msg As String msg = msg & "Do you want to enter more entries for
>>>> this consignment? " If MsgBox(msg, vbYesNo, "PCTL - Confirmation! ") =
>>>> vbYes Then DoCmd.GoToRecord , , acNewRec CartonNo.SetFocus
>>>> ConsignmentNo = N_ConsignmentNo CollectionDate.Value =
>>>> CmbCollectionDate Grade.Value = CmbGrade ClientCIN = CIN
>>>> ClientName.Value = CmbClientName Else DoCmd.GoToRecord , , acNewRec
>>>> N_ConsignmentNo.SetFocus End If End Sub Here also there is a small
>>>> problem that while clicking on the Yes/No button of msgbox its still
>>>> there on the second attempt/click i can go further Regards, Khalid ---
>>>> In
>>>>
>>>
>>
> ms_access_professionals@yahoogroups.com<mailto:ms_access_professionals@yahoogroups.com>
>>>> , <JohnV@...<mailto:JohnV@...>> wrote: Khalid-
>>>>
>>>> You should be using a single form and provide unbound fields for
>>>> Consignment and CIN values. Code you write for those unbound controls
>>>> would "lookup " the matching record or provide a blank record partially
>>>> filled in if none found.
>>>>
>>>> When your control is bound, any change by the user changes the value of
>>>> the field in the current record. It does not provide any sort of
>>>> search. With your design, users will be updating records that shouldn't
>>>> be changed!
>>>>
>>>> John Viescas, Author
>>>>
>>>> Microsoft Access 2010 Inside Out
>>>>
>>>> Microsoft Access 2007 Inside Out
>>>>
>>>> Microsoft Access 2003 Inside Out
>>>>
>>>> Building Microsoft Access Applications
>>>>
>>>> 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>
>>>>
>>>> [mailto:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> ] On Behalf Of Khalid Tanweer
>>>>
>>>> Sent: Friday, August 30, 2013 8:04 AM
>>>>
>>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>> Subject: Re: [MS_AccessPros] Need help on a from and its subform
>>> (Collection
>>>>
>>>> Voucher)
>>>>
>>>> Hi john,
>>>>
>>>> The reasoning is that i want when user selects a ConsignmentNo and
>>>> ClientCIN (CIN stands for Client identification number) the entries in
>>>> the subform are for the subject ConsignmentNo and ClientCIN. If
>>>> ConignmentNo changes OR the ConsignmentNo is same but ClientCIN changes
>>>> entries on the subform refer to it. Each time user may not have to
>>>> enter ConsignmentNo and ClientCIN
>>>>
>>>> Regards,
>>>>
>>>> Khalid
>>>>
>>>> --- In
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> , John Viescas <JohnV@...<mailto:JohnV@...>>
>>>>
>>>> wrote:
>>>>
>>>>>
>>>>
>>>>> Khalid-
>>>>
>>>>>
>>>>
>>>>> What is your reasoning for having a subform? All the fields are from
>>>>> ONE table, so they should all be on one main form.
>>>>
>>>>>
>>>>
>>>>> Note that if CollectionDate has an expression as its Control Source,
>>>>> you cannot assign a value to it, so your AfterUpdate of ConsignmentNo
>>>> will fail.
>>>>
>>>>> CollectionDate should be bound to the field, and you should copy the
>>>>> value for the date in the AfterUpdate of ConsignmentNo if that's what
>>>>> you really want to do. However, if CollectionDate is inherited from>
>>>> the Consignment Number table, there's no reason to replicate the date>
>>>> in the CollectionVoucher table.
>>>>
>>>>>
>>>>
>>>>> John Viescas, Author
>>>>
>>>>> Microsoft Access 2010 Inside Out
>>>>
>>>>> Microsoft Access 2007 Inside Out
>>>>
>>>>> Microsoft Access 2003 Inside Out
>>>>
>>>>> Building Microsoft Access Applications 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>
>>>>
>>>>> [mailto:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> ] On Behalf Of Khalid> Tanweer> Sent: Thursday, August 29, 2013 12:57
>>>> PM> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>>> Subject: Re: [MS_AccessPros] Need help on a from and its subform>
>>>> (Collection> Voucher)>> John,> you are right of asking about it>>
>>>> Table "CollectionVoucher " has following fields:
>>>>
>>>>> ConsignmentNo, Text, 9 (Row Source: SELECT [Consignment>
>>>> Number].ConsignmentNo FROM [Consignment Number]; ) Display Control:
>>>>
>>>>> Combo box
>>>>
>>>>>
>>>>
>>>>> ClientCIN, Number, Double (Row Source: SELECT Clients.ClientCIN FROM
>>>>> Clients ORDER BY Clients.ClientCIN; ) Display Control: Combo box>>
>>>> ClientName, Text, 30 (Row Source: SELECT Clients.ClientName FROM>
>>>> Clients ORDER BY Clients.ClientName; ) Display Control: Combo box>>
>>>> CollectionDate, Date/Time>> CartonNo, Text, 4>> Grade, Text, 1 (Row
>>>> Source: SELECT Products.Grade,> Products.ProductName FROM Products; )
>>>> Display Control: Combo box>> ProductName, Text, 20 (SELECT
>>>> Products.ProductName FROM Products ORDER> BY Products.ProductName; )
>>>> Display Control: Combo box>> ProductQty, Number, Double>>
>>>> WeightOfCarton, Number, Double>> Rate, Number, Double>> Amount,
>>>> Number, Double> ------------> In subform i have not put fields
>>>> "ConsignmentNo ", "ClientCIN ",> "ClientName ", "CollectionDate ",
>>>> "Grade "> -------------------------> on the main form i have put
>>>> fields:
>>>>
>>>>> ConsignmentNo
>>>>
>>>>> Row Source: SELECT [Consignment Number].ConsignmentNo, [Consignment>
>>>> Number].CollectionDate, [Consignment Number].Grade FROM [Consignment>
>>>> Number] ORDER BY [Consignment Number].ConsignmentNo;)> Event: After
>>>> Update:
>>>>
>>>>> Private Sub ConsignmentNo_AfterUpdate()
>>>>
>>>>> CollectionDate.Value = CmbCollectionDate
>>>>
>>>>> Grade.Value = CmbGrade
>>>>
>>>>> End Sub
>>>>
>>>>>
>>>>
>>>>> CollectionDate (Name:CmbCollectionDate) Control Source:
>>>>
>>>>> =ConsignmentNo.Column(1)
>>>>
>>>>>
>>>>
>>>>> Grade (Name: CmbGrade
>>>>
>>>>> Control Source: =ConsignmentNo.Column(2)>> ClientCIN, Row Source:
>>>> SELECT Clients.ClientCIN, Clients.ClientName> FROM Clients;>>
>>>> ClientName (Name:CmbClientName)> Control Source: =ClientCIN.Column(1)
>>>>>> Hope you got the sceniaro>> regards,> Khalid> --- In
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> , John Viescas <JohnV@>> wrote:
>>>>
>>>>>>
>>>>
>>>>>> Khalid-
>>>>
>>>>>>
>>>>
>>>>>> That makes no sense to have the same table as the Record Source for
>>>>>> both a form and a subform. Here are the fields you listed for>
>>>> CollectionVoucher:
>>>>
>>>>>>
>>>>
>>>>>> 5- CollectionVoucher (table)
>>>>
>>>>>> Fields: CartonNo, Text, 4
>>>>
>>>>>> ProductName
>>>>
>>>>>> ProductQty
>>>>
>>>>>> WeightOfCarton, Number, Double>> Rate>> Amount>>>> Nowhere
>>>> do I see ConsignmentNo, ClientCIN, or any fields to relate>> to
>>>> Products. Please clarify.
>>>>
>>>>>>
>>>>
>>>>>> John Viescas, Author
>>>>
>>>>>> Microsoft Access 2010 Inside Out>> Microsoft Access 2007 Inside
>>>> Out>> Microsoft Access 2003 Inside Out>> Building Microsoft Access
>>>> Applications 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>
>>>>
>>>>>> [mailto:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> ] On Behalf Of Khalid>> Tanweer>> Sent: Thursday, August 29, 2013
>>>> 9:18 AM>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>>>> Subject: Re: [MS_AccessPros] Need help on a from and its subform>
>>>>> (Collection>> Voucher)>>>> Thanks John,>> Nice to be in
>>>> contact with you after a long time.
>>>>
>>>>>>
>>>>
>>>>>> Record Source of outer form is "CollectionVoucher " and the same is
>>>>>> for subform. I did'nt meant its edited in subform.
>>>>
>>>>>>
>>>>
>>>>>> This is what i tried, you may suggest any new table and the way to
>>>>>> solve my problem, which i have mentioned at the bottom of my
>>>> question.
>>>>
>>>>>> I repeat it again:
>>>>
>>>>>> (On the main form "Collection Voucher " i need to enter OR select>
>>>>> which ever is suitable or required "ConsignmentNo then>>
>>>> "CollectionDate ", Grade, ClientCIN, ClientName>>>> On the subform
>>>> get inputs for: CartonNo (these would be serial No of>> Carton like
>>>> 1,2,3....) ProductName (in its dropdown list only those>> items
>>>> should display which "Grade " is selected on the main form.
>>>>
>>>>>> ProductQty
>>>>
>>>>>> WeightOfCarton
>>>>
>>>>>> Rate
>>>>
>>>>>> Amount
>>>>
>>>>>>
>>>>
>>>>>> What i require is that for a selected "ConsignmentNo " and selected
>>>>>> "ClientCIN " entries made in subform should save and viewed for>>
>>>> "ConsignmentNo " and "ClientCIN ">>>> If we select or change
>>>> "ConsignmentNo " and "ClientCIN " on the main>> form, subform entries
>>>> be saved for them seperately)>>>> regards,>> Khalid>>>> ---
>>>> In
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> , John Viescas>> <JohnV@>>> wrote:
>>>>
>>>>>>>
>>>>
>>>>>>> Khalid-
>>>>
>>>>>>>
>>>>
>>>>>>> I don't see how CollectionVoucher is related to any of the other
>>>> tables.
>>>>
>>>>>>> You say that's edited in a subform. What is the Record Source of
>>>>>>> the outer form?
>>>>
>>>>>>>
>>>>
>>>>>>> John Viescas, Author
>>>>
>>>>>>> Microsoft Access 2010 Inside Out>>> Microsoft Access 2007
>>>> Inside Out>>> Microsoft Access 2003 Inside Out>>> Building
>>>> Microsoft Access Applications SQL Queries for Mere>>> Mortals
>>>> http://www.viescas.com/ (Paris, France)>>>>>> P.S. Thanks - and
>>>> always glad to help!
>>>>
>>>>>>>
>>>>
>>>>>>> -----Original Message----->>> From:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>>>>> [mailto:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>> ] On Behalf Of>>> Khalid Tanweer>>> Sent: Thursday, August 29,
>>>> 2013 6:30 AM>>> To:
>>>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>>>>
>>>>>>> Subject: [MS_AccessPros] Need help on a from and its subform>>
>>>>> (Collection>>> Voucher)>>>>>> Hi all,>>> i have returned
>>>> to the group after a long period. Previously i>>> gained lot of
>>>> information, help and knowledge from members and>>> MVP's like John
>>>> (special thanks to him.) I am using Access 2003.
>>>>
>>>>>>> Any suggestions for changing or removing any table already trying
>>>>>>> to use in the below form would be appreciated with thanks.
>>>>
>>>>>>> I have a form "Collection Voucher ">>>>>> Tables are:
>>>>
>>>>>>> 1- Consignment Number (table)>>> fields: ConsignmentNo
>>>> (text,9-input mask: 9999\-A\-99;0;-)>>> CollectionDate>>>
>>>> (Date/Time) form "Add New Consignment Number " is already made to>>>
>>>> add new consignmentNo>>>>>> 2- Clients (table)>>> fields:
>>>> ClientCIN, Number, double>>> ClientName, text, 30>>> other fields
>>>> for this form are not required for above form Form>> "AddClient ">>
>>>>> is already made to add new clients>>>>>> 3- ProductGrade
>>>> (table) (Grade are "A ", "B ")>>> field: Grade, text, 1>>>>>>
>>>> 4- Products (table) (These are items which we send through our>>>
>>>> cargo)>>> Fields: Grade (Lookup Row Source is "ProductGrade ">>>
>>>> ProductName, text, 20>>> Form "Add New Products " is already made to
>>>> add new Products>>>>>> 5- CollectionVoucher (table)>>> Fields:
>>>> CartonNo, Text, 4>>> ProductName>>> ProductQty>>>
>>>> WeightOfCarton, Number, Double>>> Rate>>> Amount>>>>>> On
>>>> the main form "Collection Voucher " i need to enter OR select>>>
>>>> which ever is suitable or required "ConsignmentNo then>>>
>>>> "CollectionDate ", Grade, ClientCIN, ClientName>>>>>> On the
>>>> subform get inputs for: CartonNo (these would be serial No>>> of
>>>> Carton like 1,2,3....) ProductName (in its dropdown list only>>>
>>>> those items should display which "Grade " is selected on the main form.
>>>>
>>>>>>> ProductQty
>>>>
>>>>>>> WeightOfCarton
>>>>
>>>>>>> Rate
>>>>
>>>>>>> Amount
>>>>
>>>>>>>
>>>>
>>>>>>> What i require is that for a selected "ConsignmentNo " and
>>>> selected>>> "ClientCIN " entries made in subform should save and
>>>> viewed for>>> "ConsignmentNo " and "ClientCIN ">>>>>> If we
>>>> select or change "ConsignmentNo " and "ClientCIN " on the main>>>
>>>> form, subform entries be saved for them seperately>>>>>> Thanks
>>>> in advance for supporting as my M.D is pressing hard to use>>> this
>>>> form for our Consignment/collection of 30th August.
>>>>
>>>>>>> Khalid Tanweer
>>>>
>>>>>>>
>>>>
>>>>>>>
>>>>
>>>>>>>
>>>>
>>>>>>>
>>>>
>>>>>>> ------------------------------------
>>>>
>>>>>>>
>>>>
>>>>>>> Yahoo! Groups Links
>>>>
>>>>>>>
>>>>
>>>>>>
>>>>
>>>>>>
>>>>
>>>>>>
>>>>
>>>>>> ------------------------------------
>>>>
>>>>>>
>>>>
>>>>>> Yahoo! Groups Links
>>>>
>>>>>>
>>>>
>>>>>
>>>>
>>>>>
>>>>
>>>>>
>>>>
>>>>> ------------------------------------
>>>>
>>>>>
>>>>
>>>>> Yahoo! Groups Links
>>>>
>>>>>
>>>>
>>>> ------------------------------------
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>> ------------------------------------
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>> ------------------------------------
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>> ------------------------------------
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>> ------------------------------------
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
>
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (55) |
Tidak ada komentar:
Posting Komentar