Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2012
    Posts
    20

    Unhappy Unanswered: Linking combo Box

    Hi Guys,

    Please help if you can. Ive been searching for the last couple of hours and I cant find a solution!!

    I have a form and a subform and would like to link a combo box to the subform. The form is called Delivery Note and the subform Delivery Note Details. The combo box is pulling information however from two different tables, Commercial Invoice and Commercial Invoice Details.

    When on the delivery note form I select the commerical invoice number I would like the invoice and invoice details to display on the form. I have the invoice part working fine, but as the details are on a continuous form and more than 1 record i dont know how to get all the records related to the particular invoice number to show up!

    Any ideas or coding or any tips would be very helpful!!!

    Thanks

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You can set the subform to either Continuous or Datasheet to ensure that multiple records are displayed at once.

    Assuming that your relationships are in place and you have set the parent/child field links between the form and the subform, the Access gnomes should do the rest when you select a record in the main form.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Nov 2012
    Posts
    20
    Thank you for the quick reply.

    The links of master and child fields are in place. The relationships between the tables arent so clear, Im sorry im having a blonde moment and cant think what to do. I have:

    Commercial Invoice linked 1 to many with Commercial Details
    Delivery Note linked 1 to many with Delivery Note Details

    I'm guessing I need to link Commerical Invoice and Delivery Note together and Commerical Details and Delivery Note Details together? If so Im not too clear as to what kind of relationship they would be?

    Sorry I am still learning access so my questions may sound stupid.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're on the right track. However, there are a few things that do not ring true. If I understand it, you are selecting a value within a combo box, and that combo box's data source is a list of commercial invoices. The form that holds the combo box then displays a record for the related delivery note, and you want the subform to show the related DN details. Is that correct? If so, you need to define a link between the commercial invoice records and the delivery note details. (I would imagine that there is an order number field that is common to both - that's how our SOP system works.)

    If I've misunderstood, you can always post an image of the relationships window.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Nov 2012
    Posts
    20
    Thats sort of it, but the record being selected from the commercial invoice is then being pulled onto a delivery note and storing the information in the delivery note table. This bit is working fine

    but...

    Then the commerical invoice details are the details I want to come up as the delivery note details. This is the bit I cant work out and I dont no of a solution as its seperate tables to commerical invoice and delivery note but they are both joined to the respective tables with a one to many relationship, do i need to create a relationship between these two?

    Sorry its confusing!

    My colleagues dont want to retype everything out twice so are wanting an easier way to do it.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    If you are creating a delivery off the back of an invoice, then you will need a link between the two. Otherwise there will be no way to say which DN belongs to which IN.

    If this form is to create the DN from the IN, things get a little more complicated.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Nov 2012
    Posts
    20
    Ok thank you for your help so far. I have now created a One to many link between the delivery note and commercial invoice.

    I thought it was going to get more complicated, nothing in life is simple, but I want to suceed at this. Do you know of a way to create a DN from the IN?

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Back up a minute. Are you sure you want the relationship to be one-to-many? I mean, are you going to be creating more than one delivery note from one invoice? If not, you need to redefine the relationship to prevent duplicates.

    This also has a bearing on creating the DNs from the IN information. If you're just creating one DN per IN, you need to copy the IN header record into the DN header table (with whatever modifications are required), and then copy the IN detail records into the DN detail table (again, with required modifications). OTOH, if you need to create multiple DNs per IN, you will need some method to indicate which IN lines are being delivered on the current DN.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Nov 2012
    Posts
    20
    Yep your right one to many was the wrong choice, I am creating one DN for one IN. Thank you!

    Ok Ill try your suggestion and see how I get on with it. Thank you for taking some time to explain things to me I appreciate it

  10. #10
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome - happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  11. #11
    Join Date
    Nov 2012
    Posts
    20
    Sorry Ive been trying for an hour with no luck, Im guessing its simple and me being silly.

    How do you to copy the IN header record into the DN header table?

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The following untested air code makes the following assumptions:
    • The field names in both header tables are the same
    • The form has a control for each field
    • The controls' names are the underlying table names with standard form prefixes
    • The code is invoked from a command button
    • All the fields are stored as text, with the exception of the date and customer address sequence number

    Code:
    Private Sub cmdConfirmDelivery_Click()
    '2013-Jan-08
    'Generate an SQL statement to create a copy of the current commercial invoice header
    'details to the delivery note header details table
    
    Dim strSQL As String
    Dim varCheck As Variant
    
    varCheck = DLookup("[OrderNumber]", "tblDeliveryNoteHeader", "[OrderNumber] = '" & Me.txtOrderNumber & "'")
    
    If Not IsNull(varCheck) Then
    
    'This invoice has already been despatched
    MsgBox "A despatch note exists for this order - exiting.", vbCritical, "Already despatched"
    Exit Sub
    
    End If
    
    'No despatch note exists for this invoice - create the header
    strSQL = "INSERT INTO tblDespatchNoteHeader (OrderNumber, InvoiceNumber, CustomerAccount, CustAddSeq, CustPORef, DespatchDate) "
    strSQL = strSQL & "VALUES ('" & Me.txtOrderNumber & "', '" & Me.cmbInvoiceNumber & "', '" & Me.txtCustomerAccount & "', "
    strSQL = strSQL & txtCustAddSeq & ", '" & Me.txtCustPORef & "', #" & Format(Me.txtDespatchDate, "yyyy-mm-dd") & "#);"
    
    CurrentDb.Execute strSQL
    
    End Sub
    Hopefully this should point you in the right direction.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  13. #13
    Join Date
    Nov 2012
    Posts
    20
    Thank you very much it works

  14. #14
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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