Hi Hookom-
There is no Primary key. It is just a simple data entry form, i have made a report for it asking to input date range between ...
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
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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
>
>
==============================================
> 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 (51) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar