Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Help with new record (Acc 97 sr2 on 95b)

    I have created a form to produce invoices as follows:

    The form is tied to the Job table so that selecting a job # via a combo box, the Customer name, job details and invoice number from invoice table (to show if the job has an invoice already against it) are shown on the form.

    A subform is tied in data entry mode to the Invoice table which has an autonumber field.

    If the job is to be invoiced, a button on the main form is pressed and the Job# from the combo box is written into the job field on the subform, which creates a new record and triggers the autonumber field to provide a unique invoice number. A button on the subform opens a form tied to the Invoice table and shows the new record which is then printed.

    **My Problem**

    If a second invoice is to be done, I need the subform to go to a new record before the the number is inserted, otherwise it just overwrites the existing number.

    My idea was when the cmd button is clicked to insert the job# into the subform, the subform field would be checked to see if there was a value in it and if so the subform would be rolled to a new record.

    I cannot see how to cause the subform to go to a new record~!!!!!

    WARNING - If the answer includes recordsets I will need detailed instructions!

    Thanks in advance to all who give this a go

    Regards, Allan
    "Heading for the deep end"

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Help with new record (Acc 97 sr2 on 95b)

    I think I'm a bit confused here - it appears that you are showing (at most) one invoice number on the main form, but a job can have multiple invoices. Shouldn't the fact that a record shows up in the subform indicate that there is an existing invoice (or invoices)? If you take that approach, any time the subform control gets focus you could simply set the form to a new record and put in the job number.

    On the other hand, if you use the subform link controls to tie Invoice records to a Job, then the subform will automatically add the Job Number to the Invoice record any time you insert a new record. That's probably the easiest way to solve the problem. Hope this helps.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Help with new record (Acc 97 sr2 on 95b)

    It's the actual "set the form to new record" bit that has be stumped. While the rest of it may sound a bit screwy, it actually does just what I want.

    So, how do I do the "set form to new record" bit using code?

    I give background to my questions to try and let people know what I am trying to achieve.

    Regards, Allan
    "Heading for the deep end"

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Help with new record (Acc 97 sr2 on 95b)

    Your background is quite useful, as it helps to understand the problem, and lots of people just post a brief question with no explanation of what they are trying to accomplish.

    I actually answered your question in my previous post - use the On Got Focus event to execute code that looks like
    DoCmd.GoToRecord , , acNewRec
    You will also need to set the Job number since your subform doesn't appear to be linked.

    I still believe that this makes your solution more complicated (and therefore more fragile) than it needs to be. We very seldom see a subform that isn't linked, and when we do there are nearly always problems.
    Wendell

Posting Permissions

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