Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2009
    Posts
    13

    Unanswered: Creating an Invoice(Order) Unbound w/ Subform

    I started building a database for my work and it has gotten real complex, real quick, which has left me with some problems. I am going to list a few and you can pick and chose or try to help with the whole thing. (if you need some additional info about my DB, you can check here or just let me know.

    So here we go,
    I am creating a form to create invoices/orders for services provided for each Client each month. The invoice is broken into 2 tables (tblInvoice & tblInvoiceDescription because they can have more than 1 service per month) with the following fields:

    tblInvoice: InvoiceNumber (AutoNumber), ClientID (connected to tblClient), Date

    tblInvoiceDetails: InvoiceNumber (from tblInvoice), ServiceID (from tblServicesProvided which provides our "products" with description and price), NumberParticipating (which is supposed to be a count of how many employees are enrolled per client [one of my main problems which i will address later]), and Price

    I need a form that will allow the user to select the Client (maybe a combo box on ClientID), enter the date, select the services provided (may be more than one) and save & print the invoice. If at all possible, i would like to have an unbound form so that i can have more control over when and if the record saves.

    Now to the problems:

    - NumberParticipating is a calculated field that counts employees enrolled for each client and is used to multiply by the service price to get the cost per service. This number needs to be up to date everytime an invoice is created and the number participating in that month needs to be stored in the tblInvoiceDescription because Clients hire and fire multiple employees in the course of a month so the number may vary invoice to invoice (since NumberParticipating and the services provided are being saved in each invoice, there is no need to save the total cost per service). The problem is, i cannot get this number to automatically pull up when the client is selected. I don't know if i need to show this number on the form or just store it somehow, create a new field in some table, use a query, pull it up in a combo box and pull it from the column, calculate the field on the form, etc.

    - User has to be able to select multiple services so i figured i needed a datasheet subform to allow that but maybe a list box or a group of checkboxes. If possible i would like to use a method other than the datasheet and i would like the form to automatically add service choices when i add them to tblServicesProvided. Much like the switchboard does (i may be able to copy some of the code from there but haven't looked at it yet).

    - After the info is entered, i need the correct info to go to the correct tables. The problem i think i will have with this if i use an unbound sub form (which i don't want to do), how do i connect the invoice with the multiple services provided? this may become apparent once i fix the above problems.

    this is not the entire list of problems but i am hoping that i may eliminate a large amount of them with these fixes.

    This is like a novel but i wanted to provide the most information possible. I would like to go ahead and thank you if you have read this far and hopefully there are some simple fixes for me.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    1) I'm not clear on where the number participating is supposed to come from.

    2) You could use a listbox to select multiple services, but it wouldn't be practical unless they would all use the same quantity. Otherwise you probably need a subform to be able to enter quantities individually.

    3) Presuming you go with the unbound solution (I probably wouldn't), you would append a record to the main table, get the InvoiceNumber, then use that when appending to the detail table.
    Paul

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree with Paul on all three points, but particularly on #3! If you think things have "gotten real complex, real quick" as Bachman-Turner Overdrive said "You ain't seen nothing yet!" Going to unbound forms means increasing your complexity by tenfold or more!
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jan 2009
    Posts
    13
    thanks for replying.

    1. you and I both. The number participating is a count of all the employees that are enrolled under each client. Our services are charged on a per employee/ per month basis so the NumberParticipating is much like quantity. As of right now, we are only dealing with one service but in the near future we will have multiple services, some with per person per month and some with flat monthly fees so the quanity will not be the same.

    2. Covered in # 1 above.

    3. The reason i want unbound forms is because if the users aren't following the process correctly, i am getting partial and incorrect records. if i could control when and if the bound form saves, then i would have no problem using bound forms. Below is some code from one of my other unbound forms (frmClient which creates my client records) which might help to see how i am processing the unbound data.

    Code:
    Private Sub cmdSave_Click()
    On Error GoTo Err_cmdSave_Click
    
    '--- only process Save if there is data in company name
    If IsNull([ClientName]) Then
        MsgBox "No Client Name Entered", vbOKOnly
        ClientName.SetFocus
        Exit Sub
    End If
    
        Dim rst As Recordset
        '---Opens Clients Records
        Set rst = CurrentDb.OpenRecordset("select * from tblClients")
        
        rst.AddNew
        
        '---Sets Record = form
            rst!ClientName = ClientName
            rst!ClientPhone = ClientPhone
            rst!ClientFax = ClientFax
            rst!Address = Address
            rst!City = City
            rst!State = State
            rst!Zip = Zip
            rst!EmailAddress = EmailAddress
            If Me.PlanYearStart & "" <> "" Then
            rst!PlanYearStart = PlanYearStart
                End If
            If Me.PlanYearEnd & "" <> "" Then
            rst!PlanYearEnd = PlanYearEnd
                End If
            If Me.EffectiveDate & "" <> "" Then
            rst!EffectiveDate = EffectiveDate
                End If
            rst!Eligibility = Eligibility
            rst!MERPGoal = MERPGoal
            If Me.ERContributionEE & "" <> "" Then
            rst!ERContributionEE = ERContributionEE
                End If
            rst!ERContributionEqual = ERContributionEqual
            If Me.ERContributionFam & "" <> "" Then
            rst!ERContributionFam = ERContributionFam
                End If
            rst!EarlierPeriodClaims = EarlierPeriodClaims
            If Me.EarlierClaimsLimit & "" <> "" Then
            rst!EarlierClaimsLimit = EarlierClaimsLimit
                End If
            rst!CreditsGiven = CreditsGiven
            rst!InsuranceCarrier = InsuranceCarrier
            
        
            
        rst.Update
        rst.Close
        Set rst = Nothing
        MsgBox "Record Saved Successfully", vbOKOnly
        Call cmdClear_Click
        
    
    
    Exit_cmdSave_Click:
        Exit Sub
    
    Err_cmdSave_Click:
        MsgBox Err.Description
        Resume Exit_cmdSave_Click
    End Sub

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It is not that difficult to use the form's before update event to validate data and set

    Cancel = True

    to stop the update with a bound form. You also have available the Undo method. You can cancel the unload event if they exit the form without using your save button. In other words, you probably have more tools available to control a bound form than you realize. That said, I've used unbound forms under certain circumstances, and you may decide this is one of those.
    Paul

  6. #6
    Join Date
    Jan 2009
    Posts
    13
    So i might be moving toward bound forms, but as i try to create a form to create invoices and view previous, i end up with only a way to view info. I created a query with all the info i need and based a form on that info. it will pull up any records i have created but won't let me create a new one. Then i tried to base the form on the tables but same except now there is no existing records. I have tried using the Orders form from the Northwind database as a guide but i can't seem to get it. If i could get my form to work exactly like the one from the Northwind, that would be great. The minor differences are that i don't have to have a seperate Bill to and Ship to address; no need for Ship via, required date, shipped date, or discount; and quanity will be the NumberParticipating (count of number of employees in the company).

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    There are all kinds of things that will cause a query to be read only:

    Microsoft Access tips: Why is my query read-only?

    In your situation, the most common setup is a form bound to the invoice table and a subform bound to the invoice details table, with master/child links keeping them in sync with each other.
    Paul

  8. #8
    Join Date
    Jan 2009
    Posts
    13
    quick question, can you give me some details about the master/child links? is that somehthing that i need to set up or will that relationship be created for me? also, i still cannot figure out how to assign the NumberParticipating with the count of employees.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you use the subform wizard to add the subform, it will walk you through it. If you don't, they are properties of the subform control that can be set manually.

    I don't understand your structure enough to say how to get the number. If there's a table that lists them, you could a use recordset or DCount/DSum to count them. How/where are they stored in the data?
    Paul

  10. #10
    Join Date
    Jan 2009
    Posts
    13
    Let me walk thru the flow real quick:

    1- User Creates a Client Profile (tblClient)
    2- User Enrolls X number of employees under the Client (tblEmployees)
    3- On 25th of each month, User creates invoices to bill Clients. This billing is based on how many employees are enrolled (As of the day the invoice is ran. this is important becuase the number has to be up to date. For this reason, i don't have that info stored, i calculate it by counting the employee records per client. I do, however, need to store this number in the Invoice (tblInvoiceDesciption.NumberParticipating) so that when we go back and look at a certain invoice, we know how many were enrolled at the time of the invoice and not the updated number.)

    Does that help any? i can send you my front end but its quite complex and since i am learning as i build, it is not documented well and may contain loads of bogus and useless crap. I am trying to get it functional and then go back and overhaul it later. Thanks for your help by the way.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, I'm not getting email notifications of your replies. Sounds like any of the methods I mentioned would work. DCount would probably be the simplest for you. Here's a nice reference on the syntax:

    General: DLookup Usage Samples
    Paul

  12. #12
    Join Date
    Jan 2009
    Posts
    13
    ok, so this is what i think i am supposed to do. On my subform for Services provided, when i select a service, why is the price not automatically updating to the corresponding price in its table? The DCount worked perfectly but now i need my price to update. I tried to use DLookup but it says that it cannot find the form i am referencing when i try to use Forms!InvoiceSub!ServiceID as the criteria.

  13. #13
    Join Date
    Jan 2009
    Posts
    13
    nevermind, i feel like a total tard. i requeryed the field and now its workin. i will let you know if i have any more problems. Thank you for your help.

  14. #14
    Join Date
    Jan 2009
    Posts
    13
    Now that i have the form working, i need to validate that all the required data is entered and then save or not save. I have done this with Unbound forms but am unfamiliar with the techniques and methods for doing so on bound forms.

  15. #15
    Join Date
    Jan 2009
    Posts
    13
    also, i am having trouble referring to a control from my invoice subform. I created a Subtotal control box that adds all the services together to give a total cost in the footer of the InvoiceSub. Now i am trying to pull that total forward to a textbox on the actual invoice form. I have tried

    - =[Forms]![Invoice]![InvoiceSub].[Form]![Subtotal]
    - [InvoiceSub].[Form]![Subtotal]

    Both give me an #Error. I assume that since i am only wanting to add totals that are on the current record, that calculating it on the subform makes the most sense but i may be wrong. any suggestions are most appreciated. Thanks.

Posting Permissions

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