Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2013
    Posts
    9

    Unanswered: Button in Form to open a different form and chose a customer based on last form

    Hello Guys, Access 2010 issue, for a Catering Database.

    Basically I have a Customer Form, which I have a New party button on it,
    this button opens up the party form to a new party, what I would like it to do is open up a new party but make the new party for the customer I had selected in the previous form.

    I have tried the GoTo macro's but cannot seem to get it to work, A little guidance and/or help on this would be greatly appreciated.

    I am thinking on clicking the button it will need to get the Customer ID, and then open the party form, create new party, and paste in the Customer ID, which then updates the Name - Date - Address - Company Fields.

    How would I go about getting this to work?

    I have tried this. On the Customer Form Button,

    Code:
    DoCmd.OpenForm "New Party List", acNormal, "", "", , acNormal
        On Error Resume Next
        DoCmd.GoToRecord , "", acNewRec
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
    And this as the New Party List Form OnLoad

    Code:
    Me.[Name of Customer] = Forms("Customers")("Customer Name")
    I get this error.

    The Value you entered isn't valid for this field

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You speak of passing the CustomerID, but your attempts shows you using the [Customer Name]. Since the appropriate way really is to use a unique ID (there are really very, very few unique names!) so that's the method I'll use, here:

    In the Customers Form

    Code:
    Private Sub CustomerFormButton_Click()
    If Not IsNull(Me.CustomerID) Then
      DoCmd.OpenForm "New Party List", , , , , , Me.CustomerID
     Else
      MsgBox "A CustomerID Must Be Entered First!"
     End If
    End Sub

    In the New Party List Form

    Code:
    Private Sub Form_Load()
    
    If Not IsNull(Me.OpenArgs) Then
        DoCmd.GoToRecord , , acNewRec
        Me.CustomerID = Me.OpenArgs
    End If
    
    End Sub

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2013
    Posts
    9
    Thanks MissingLing,
    Sorry it was meant to be CustomerName
    And form is NewPartyList

    I have put this code in instead.
    In the Customers Form


    Code:
    Private Sub Command55_Click()
    If Not IsNull(Me.CustomerName) Then
      DoCmd.OpenForm "NewPartyList", , , , , , Me.CustomerName
     Else
      MsgBox "A CustomerName Must Be Entered First!"
    End If
    End Sub
    In the New Party List Form


    Code:
    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        DoCmd.GoToRecord , , acNewRec
        Me.CustomerName = Me.OpenArgs
    End If
    End Sub
    I am getting this error.

    Compile Error:
    Method or Data member not found.

    Thanks again.
    Last edited by TEDOC; 04-02-13 at 01:49.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    All of the 'methods' used here are time-worn standards; that error message probably means that have the name of something is spelled incorrectly. Your original code

    Forms("Customers")("Customer Name")

    indicates that Control name, on the Customers Form, is

    Customer Name

    with a space between Customer and Name, but your new code

    DoCmd.OpenForm "NewPartyList", , , , , , Me.CustomerName


    uses the name

    CustomerName

    without a space between Customer and Name.

    Check that this and all other names in the code match the actual names on the Forms.

    The Access Gnomes don't play horseshoes...being 'close' never works with them.

    Linq ;0)>
    Last edited by Missinglinq; 04-02-13 at 09:28.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2013
    Posts
    9
    I changed the entire naming convention on the database so no spaces were in any names.

    So CustomerName is the correct name for the field.

    The OpenArgs is returning the same name every time?

    Even tho it should be changing depending on the record active?

    Would it be easier if I uploaded my database for you to have a read? see if it looks right?

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'd be happy to take a look at it, if you could save a copy in an earlier file format, 2003 would probably be best; this will allow more people here to help, if need be. Zip the file up and attach it to a post here.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2013
    Posts
    9
    Thanks mate,

    Here is the database, i had a bit of trouble converting it to 2003.

    Everything seems to be working how it should except for the NewPartyList form.

    the buttons do not work and the onload function does not work.

    But you will get the idea of what i am trying to achieve.

    Thanks again.
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2013
    Posts
    9
    Did you have any luck MissingLing?

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Actually, I've spent a number of hours working on it, between other things, and I have no idea what is going on, or even how you've managed to do some o f the things you've done!

    CustomerName is defined as Text in the Customers Table, which is appropriate, but CustomerName in the Party Table, is defined a Number Datatype! Even stranger, in the Party Table, although it is clearly defined as a Number Datatype, the data that Table holds, in that Field , is patently...Text! I couldn't figure how you could enter Text in a Field defined as a Number! Finally decided to try entering a name in the Field in the Table, myself, got a 'Not in List' error message, and figured it out! The $%^& Field is a Lookup Field in the Table!

    You need to have a look at

    The Evils of Lookup Fields in Tables

    I've got to step away tonight, but acknowledge this post, when you see it, so that I'll get an email reminder about the thread, and when I get a chance tomorrow, I'll see what I can do to resurrect this for you!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Apr 2013
    Posts
    9
    Yeah, I found it weird when I created the tables that the lookup fields get created as numbers instead of text.

    Anyway, I have read through that link you supplied and it seems that lookup fields are a big no no, but it doesn't explain what to do for an alternative. I'm assuming a query or something.

    I am not sure if converting it to 2003 broke anything, but we shall assume that everything is working.

    Basically what the database is for is to log parties and menus and packing lists for 1 week. Each Party has the same menu but may have different quantities the same as the packing list.

    after the 1 week it will get archived and then the party numbers / parties will be deleted and restarted at the beginning of the week.

    I still need to create the archiving area, and also create the report section but I really wanted this button thing sorted first.

    I really appreciate you taking a look at if for me buddy.

  11. #11
    Join Date
    Apr 2013
    Posts
    9
    Missingling, I hope you had good weekend.
    Any luck on finding out what mess I have created. Haha

  12. #12
    Join Date
    Apr 2013
    Posts
    9
    Is anyone else able to help me with this issue I am having?

Posting Permissions

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