ACCESS FEED: [MS_AccessPros] RE: Need help on a from and its subform (Collection Voucher)

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) --- In ms_access_professionals@yahoogroups.com, <khalidtanweerburrah@yahoo.com> wrote: 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 , <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 ] On Behalf Of Khalid Tanweer
Sent: Friday, August 30, 2013 8:04 AM
To: 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 , John Viescas <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 ] On Behalf Of Khalid
> Tanweer
> Sent: Thursday, August 29, 2013 12:57 PM
> To: 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 , 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 ] On Behalf Of Khalid
> > Tanweer
> > Sent: Thursday, August 29, 2013 9:18 AM
> > To: 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 , 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 ] On Behalf Of
> > > Khalid Tanweer
> > > Sent: Thursday, August 29, 2013 6:30 AM
> > > To: 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