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