none
Services Template Combox Part 2 RRS feed

  • Question

  • I am trying to add new fields in the Invoices section of the Services templates so that when I choose the combo box, the new fields I added, Size, Manufacturer, Model. Type is automatically filled on the invoices along with fields (Service Code, Service Description, Default Hourly Rate, and Total Price) that are already there.  I added fields to the Invoice tables as per the video instructions per Deekpak (how to add fields to a template) however, I need something  a bit different ).  Below are my screen shots that I completed. For some reason, when I choose the combo box, the new fields that I added, Sizes, Manufacturer (Manu), Model, and Type are not displaying on the invoice below are screenshots. Any help to set me on the right path will be deeply appreciated.  

     

    SERVICE DETAIL FORM-NEW FIELDS ADDED

                      

     CBO ROW SOURCE

    Macro

    Thursday, June 1, 2017 4:27 AM

All replies

  • Hi Kerry01,

    I try to check the screen shots above.

    you had mentioned that,"when I choose the combo box, the new fields that I added, Sizes, Manufacturer (Manu), Model, and Type are not displaying on the invoice below are screenshots."

    from your description , it looks like you want to add combobox, but I did not see any combobox there.

    did you added the combobox as I showed in your other thread?

    Reference:

    Services Template Combox

    after that I can see a "Service Detail" form. which looks like you had edited and insert some textboxes.

    but again I did not find any combobox here too.

    then I can see that you had post the picture of properties of combobox.

    can you tell me where is this combobox? and did you added it on "Invoice" form or "Service Detail" form?

    are these both form are connected ? as per your logic?

    I did not understand why you posted 2 Forms. I understand that you only want to add combobox in "Invoice" table.

    if you want to add combobox on both forms then you just need to add combobox control and set its Control Source and Row Source property to make it work.

    please try to correct me, if I misunderstand something in your above description, so that I can try to provide further suggestion to you.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 2, 2017 1:28 AM
    Moderator
  • Hi Deepak, thanks again for your help.

    The combo box is already on the invoice under the "Services" label .  I wanted only one combo box on the invoice when I choose the combo box, "Services" it will automatically populate the Size, Manufacturer, Model ,Type automatically filled on the invoices along with fields (Service Code, Service Description, Default Hourly Rate, and Total Price that are already there). 

    The invoice (Line Items) gets the information to populate the fields from the Service Detail form.  I am just trying to add my fields, Size, Manufacturer, Model, Type along with  Service Code, Service Description, Default Hourly Rate, and Total Price on the combo box that's already there.  

    I goofed putting the two invoices on my question, it should only be one invoice. 

    Thanks so much for any help.

    Monday, June 5, 2017 4:47 AM
  • Hi Kerry01,

    you had mentioned that,"I wanted only one combo box on the invoice when I choose the combo box, "Services" it will automatically populate the Size, Manufacturer, Model ,Type".

    I understand your requirement and I create a small example to fill the data in control as per combobox selection.

    I use change event of combobox and try to fetch the data from table using query. you can see the code below.

    Private Sub Combo0_Change()
    
    Dim SQL As String
    Dim db As Database
    Dim rs As DAO.Recordset
    
    SQL = "select Country from tblCountry1 where CountryID =" & Me.Combo0.Value
    Set db = CurrentDb
    Set rs = db.OpenRecordset(SQL)
    Dim str As String
    str = rs!Country
    Text2.Value = str
    
    
    Set rs = Nothing
    Set db = Nothing
    
    
    End Sub
    

    you need to do the same and you need to fetch all fields that you want to display, then you can access the value of that field using recordset. you just need to fetch those values and assign them to your controls.

    hope it will give the idea to solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 5, 2017 7:39 AM
    Moderator
  • .........the new fields I added, Size, Manufacturer, Model. Type.........

    You should not have Service Code, Size, Manufacturer, Model  or Type columns in the form's table, only in the referenced ServiceLineItemsCode table.  These attributes are functionally determined by ID, so to include columns representing them in the referencing table would introduce transitive functional dependencies, and the table would not be normalized to Third Normal Form (3NF).  It would consequently be open to the risk of update anomalies.  If you are unfamiliar with the principles of normalization you might like to take a look at Normalization.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In the form the Service Code is shown in the cboServiceID combo box normal way by hiding the bound first column.  The Size, Manufacturer, Model and Type controls should be unbound text boxes with ControlSource properties as follows:

    =cboServiceID.Column(3)
    =cboServiceID.Column(4)
    =cboServiceID.Column(5)
    =cboServiceID.Column(6)

    The Column property is zero-based, so Column(3) represents the fourth column, and so on.

    Whether the DefaultDesciption is functionally determined by ID and should be treated in the same way is unclear.  If it immutably determined by ID then it should not be a column in the referencing table, and should be represented by an unbound text box with a ControlSource property of:

    =cboServiceID.Column(2)

    However, its name suggests that you might wish to edit the value in the referencing table from its default looked up in the referenced table, in which case there should be a column in the table and the value should be assigned to a bound text box in cboServiceID's AfterUpdate event procedure with:

        Me.DefaultDesciption = cboServiceID.Column(2)

    Or do similarly with a macro.

    A few other suggestions.

    1.  Do not use Type as a column or control name.  It is a reserved word in Access, and as such, should not be used as an object name.

    2. Even though the template does so, it is not good practice to use the generic ID as the column name for a key.  Always use semantically meaningful names such as ServiceID.  This makes VBA code, macros or SQL statements easier to write and read.  When designing your own databases  wherever possible, use the same name for a primary key and any foreign keys which reference it.  It is unfortunate that MS templates include this and other basic design faults in so many cases.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, June 5, 2017 10:51 AM Typo corrected.
    Monday, June 5, 2017 10:49 AM
  • Thank you very much for your great input. I will try your suggestion and Deepak's. I will let you know how it turns out.

    Kerry

    Tuesday, June 6, 2017 5:09 AM
  • Thank you very much for your help again. I will try out the procedure you completed. 
    Tuesday, June 6, 2017 5:12 AM
  • Hi Kerry01,

    Has your issue been resolved? If not, please feel free to let us know.

    Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 20, 2017 7:46 AM