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