Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2000 (Opening Form from VB)

    Hi, I have the following code attached to a command button but I would like to add an extra criteria. The command button in sitting within a tab control labelled Viewings and is called frmViewingsTest. The master form is frmProperty.

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "PURCHID=" & Me!PURCHID

    I would also like to as an extra criter where is look at the PropertyId field as well as the PURCHID field.

    The is the first phase of me trying to register an offer from a viewing at the property so I may have other questiosn if I get stuck. Hopefully no-one minds me doing this, as I have onyl be using Access for nearly 2 months now.

    Can anyone provide assistance?
    Best Regards,

    Luke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Opening Form from VB)

    The WhereCondtion argument of DoDmd.OpenForm is a string. You can create complex where-conditions by assembling this string from individual parts. If you want the records to meet all conditions, use AND:

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "PURCHID=" & Me!PURCHID & " AND PropertyID=" & Me!PropertyID

    If you want the records to meet at least one of the conditions, use OR:

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "PURCHID=" & Me!PURCHID & " OR PropertyID=" & Me!PropertyID

    The above assumes that PropertyID is a number field, like PurchID. If you want to include a condition on a text field, the value must be enclosed in quotes; you can use Chr(34) for this. Here is a fictitious example:

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "LastName=" & Chr(34) & Me!LastName & Chr(34)

    For a data/time field, the value must be enclosed in # characters, and if your database will be used on systems with non-US date settings, you must convert the date to US date format mm/dd/yyyy:

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "DateIssued=#" & Format(Me!DateIssued, "mm/dd/yyyy") & "#"

  3. #3
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    Thanks for the reply, how do I get it so the PURCHID points to the subform of frmPurchaser in the code so that the subform hs the data?: -

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "PURCHID=" & Me!PURCHID & " AND PropertyID=" & Me!PropertyID

    Luke
    Best Regards,

    Luke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Opening Form from VB)

    I'm a bit confused about the names, so I'll give a general answer.

    To refer to a control on a subform, you can use Me!SubformName!ControlName where SubformName must be the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window. To find the name of the subform as a control, open the main form in design view and click once on the subform. Don't click on the subform a second time, for that will select something on the subform. If you do this by accident, click outside the subform, then click once on it.
    The name of the subform as a control is displayed in the title bar of the Properties window and in the Name property (in the Other tab).

    See Forms: Refer to Form and Subform properties and controls for a comprehensive overview.

  5. #5
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    I maybe doing something really stupid here, but still cannot get it.

    I have ensured the name of the subform is called Subform1

    I have ammended the vb code to show: -

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "PURCHID=" & Me!SubForm1!PURCHID

    I have even tried: -

    DoCmd.OpenForm "frmOffersTest2", acNormal, , "PURCHID=" & Me!SubForm1.form!PURCHID

    The error I get now is: -

    Runtime error 2465 Access cannot find the field SubForm1, well it's obviously not going to when it's not a field name it a subform
    Best Regards,

    Luke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Opening Form from VB)

    Have you made sure that SubForm1 is the name of the subform as a control on the main form? See my previous reply for instructions. If SubForm1 is the name of the subform in the database window, and if it has a different name as a control on the main form, you'll get this error.

  7. #7
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    Hans,

    Thanks for the response. Have been able to sort it now and it works.

    Now for the next questtion.

    The PURCHID section of the subform is not populating even though the vb code is carrying across the right value, why would this be?

    Luke
    Best Regards,

    Luke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Opening Form from VB)

    Do you mean that PURCHID is a field in a subform of the opened form frmOffersTest2? If so, how is this subform linked to its parent frmOffersTest2? If not, could you explain in more detail what you mean?

  9. #9
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    I have the form:

    frmOffersTest2 which has a recordsource of tblOffers which has an autonumber field of OfferID and other fields which base the relationship of PurchID and PropertyID

    In the form there is a subform called Subform1 which has a form based on tblPurchasers.

    I click on the command button with the right code in and the PURCHID and PropertyID on the frmOffersTest do not populate. The link for the subform1 is based on PURCHID in the tblOffers.

    Anything else?

    Luke
    Best Regards,

    Luke

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Opening Form from VB)

    I am utterly confused now. Could you attach a stripped down copy of the database? See <post#=401925>post 401925</post#> for instructions.

  11. #11
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    Here it is, good luck.

    The form you should use is ViewingsTest and the command button second from the right.
    Best Regards,

    Luke

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 (Opening Form from VB)

    You haven't included tblOffers, so I can't test anything, but it isn't going to work this way.The WhereCondition argument can be used to filter the main form being opened, not a subform of that form. In general, it doesn't make sense to filter a subform, since the filtering is done automatically by the link between the main form and subform.

  13. #13
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    Sorry I am confused about the filtering?

    As far as I am aware I have not told the form to filter?
    Best Regards,

    Luke

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 2000 (Opening Form from VB)

    When you send the form a WhereCondition string, you are instructing it to filter its recordset to include only the records that match the where condition. That is what Hans is talking about. If you use Master/Child links between the parent form and the subform, the filter applied to the parent form through the WhereCondition will also limit the records on the subform through the links.
    Charlotte

  15. #15
    4 Star Lounger
    Join Date
    Mar 2005
    Location
    Leicestershire, United Kingdom
    Posts
    506
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 (Opening Form from VB)

    Right, think I know what I am doing wrong after thinking property about the problem.

    The VBA is filtering for a PURCHID which matches the one on the viewings form but obviously there is no offer reference as it does not exist in the offers table. So what I need to achieve in on the press of the button it takes the PURCHID field and PropertyID field and create a new record on a form so that the rest of the information can be populated! So any suggestions on how to do this?

    Please accept my sincere thanks in trying to achieve the end to my problem, I am a newbie and have probably really tested the patience of you guys so thank you.
    Best Regards,

    Luke

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •