Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have two tables tblCustomer and tblRental. When a customer makes a reservation, the reservation information is entered into tblCustomer (before there is a rental record for that customer) via frmCustomerEntry. I want to transfer the reservation information to tblRental when the customer checks in and clear the reservation information in tblCustomer.

    For example in frmCheckIn, powered by qryRentCalc0, I have a field Res# from tblRental. (Not all records in either of the two tables will have reservation information.). When the customer checks in on frmCheckIn, I want to copy the reservation number, if any, stored in the ReservationNumber field in tblCustomer to the Res# field in tblRental and then clear the reservation number from the ReservationNumber field in tblCustomer. TblCustomer and tblRental are joined by the CustomerID in qryRentCalc0.

    Any guidance would be appreciated

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The following two lines should transfer the value from one table to the other:

    Me.[Res#] = Me.ReservationNumber
    Me.ReservationNumber = Null

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks. Why is Res# in brackets and ReservationNumber not. Also, in what event should the code be located?

    Another idea. Maybe I should locate all the Reservation information in a separate table. Each customer could have more than one reservation but a particular rental record would only have one reservation. I need to think through how the joins would work and what the link fields would need to be.

    Tom


    [quote name='HansV' post='783868' date='09-Jul-2009 18:30']The following two lines should transfer the value from one table to the other:

    Me.[Res#] = Me.ReservationNumber
    Me.ReservationNumber = Null[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > Why is Res# in brackets and ReservationNumber not

    Res# is in brackets because of the # in the field name; spaces, punctuation and other "non-standard" characters in names cause errors unless you enclose the name in brackets (for that reason I try to avoid spaces etc.)

    > Also, in what event should the code be located?

    You wanted this to happen "When the customer checks in on frmCheckIn". I have no idea how that works.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am still having a problem in getting information transferred from tblCustomer to tblRental and then deleting the information in tblCustomer.

    QryRentCalc0 is the source for frmCustomerCheckIn. To check in someone, you use frmCustomerEntry to look up to see if the customer is already in the database. Assuming he is already in tblCustomer (since we had previously set up a new customer record which contains the reservation information using frmReservationEntry), you press a CheckIn command button and it opens the frmCheckIn. There is then a pull down menu to enter the Customer Name. Once the customer name is selected then the remaining information is entered.

    I tried to use the OnLostFocus event on the Name since the name field has to be entered to use the pull down menu. When I tab out of the name field, the Res# field is not populated. When you use the close Form command button, I get a Compile Error: Method or data member not found. The Me.ReservationNumber code is highlighted.

    I think what is happening is that it doesnít recognize Me.ReservationNumber because the new record in tblRental, which is in the process of being populated, has not been created in tblRental until the record has been saved.

    I donít know how to work around this issue.

    Any help would be appreciated.

    Tom



    [quote name='HansV' post='784013' date='10-Jul-2009 12:35']> Why is Res# in brackets and ReservationNumber not

    Res# is in brackets because of the # in the field name; spaces, punctuation and other "non-standard" characters in names cause errors unless you enclose the name in brackets (for that reason I try to avoid spaces etc.)

    > Also, in what event should the code be located?

    You wanted this to happen "When the customer checks in on frmCheckIn". I have no idea how that works.[/quote]

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What is the difference between frmCustomerCheckIn and frmCheckIn?

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry, the correct name of the form is frmCustomerEntry not frmCustomerCheckIn. FrmCustomerEntry is used to input customer information into tblCustomer. FrmCheckIn is used to enter information into tblRental. TblRental contains the information about a particular stay: room number, type of room, rental dates, rates etc.

    Tom




    [quote name='HansV' post='784396' date='13-Jul-2009 17:11']What is the difference between frmCustomerCheckIn and frmCheckIn?[/quote]

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You wrote "frmCheckIn, powered by qryRentCalc0" and "QryRentCalc0 is the source for frmCustomerCheckIn", which turns out to be frmCustomerEntry. So both forms appear to have the same record source. If that is correct, why have two forms?

    And if Me.ReservationNumber isn't recognized: is ReservationNumber a field in qryRentCalc0?

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Separate inform forms were used because data can be entered at different times. For example, a customer can make a reservation and his customer information is entered. He does not have a rental record because he has not yet checked in and it is possible he would never check in. On frmCustomerEntry only customer information is entered. On frmCheckIn the detail rental information is entered.

    Res# is a field in tblCustomer. ReservationNumber is a field in tblRental. Both Res# and ReservationNumber are fields in qryRentCalc0.

    Tom




    [quote name='HansV' post='784403' date='13-Jul-2009 17:49']You wrote "frmCheckIn, powered by qryRentCalc0" and "QryRentCalc0 is the source for frmCustomerCheckIn", which turns out to be frmCustomerEntry. So both forms appear to have the same record source. If that is correct, why have two forms?

    And if Me.ReservationNumber isn't recognized: is ReservationNumber a field in qryRentCalc0?[/quote]

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I fear that I'd have to see (a stripped down and zipped) copy of the database; I don't see an obvious reason why the code wouldn't work.

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will work on a stripped down version. Is there still a length limitation on the attachment?

    In the meantime, can you save a record without unloading it? If not, can you save it, unload it and automatically re-load it. I am still convinced that once the record is saved the field s can be copied.

    Tom



    [quote name='HansV' post='784410' date='13-Jul-2009 18:34']I fear that I'd have to see (a stripped down and zipped) copy of the database; I don't see an obvious reason why the code wouldn't work.[/quote]

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The maximum size for an attachment is now 2 MB.

    You can save a record using

    RunCommand acCmdSaveRecord

    Make sure that the record passes all validation checks before saving it, otherwise you'll get an error.

  13. #13
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A customer can be entered into the system by using frmCustomerEntry or frmReservationEntry. It is possible to combine these forms but I thought it would be clearer for the manager to have a separate Reservation form.

    The procedure is to use the FindLast to determine is the Customer is already in the system. If there are customers with the same last name you have to click the next record button to see each customer.

    If the customer is not in the system, he is entered at this point. You then go to the CheckIn button to check the guest in.

    FrmEditRecord allows you to view or edit records. For example record 108740 will display the reservation information that was manually loaded into that record. The record number is a combination of the PropertyID and the OrderID.

    Tom

    [attachment=84698:WoodyTest.zip]





    [quote name='HansV' post='784499' date='14-Jul-2009 09:03']The maximum size for an attachment is now 2 MB.

    You can save a record using

    RunCommand acCmdSaveRecord

    Make sure that the record passes all validation checks before saving it, otherwise you'll get an error.[/quote]
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I am sure that the situation is clear to you, but I'm afraid I don't understand the relationship between your description and the database that you posted. I am sorry about that.

    So I'll restrict myself to a few general remarks:
    • Create relationships with enforced referential integrity.
    • Foreign keys should have the same field size as primary keys, for example if the primary key is an AutoNumber field (i.e. a long integer), the foreign key should be a long integer, not a byte.
    • Remove unnecessary and duplicate indexes.
    • Set the Subdatasheet Name property of all tables to [none].
    • Take the customer query and the marketing query out of qryRentCalc0. The query should contain the CustomerID from tblRental, not from the customer query.
    • Don't save the record unconditionally. There is no point in trying to save the record if there is no CustomerID.

  15. #15
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your remarks and I am in the process of making those changes. I wish I could better explain how it works:
    FrmCustomerEntry
    1. When this form is opened, the Customer Entry screen appears and displays the first record in tblCustomer. This record is locked so that it can not be inadvertently changed.
    2. Click on find Last to determine whether or not the customer is already in tblCustomer
    3. Select the customer name from the pull down menu and click OK. That Customer is then displayed and can be edited if there are any changes to be made.
    4. There is an ADD NEW button if the customer was not already in the database and you want to add a new customer record
    5. To check the customer in, press the check in button. This saves the customer information in tblCustomer and opens the CheckIn form
    6. Continue by selecting the Customer Name from the pull down menu
    7. Continue by entering the balance of the rental information that will be stored in tblRental when the form is closed by clicking on the Close Form button.
    8. The operator can never directly access frmCheckIn. He can only access by using frmCustomerEntry.

    FrmCheckInEdit
    1. This form is used to review the rental information and edit if needed
    2. When this form is opened, it asks for the receipt Number of the rental record.
    3. When the receipt number is entered, the rental record is displayed.
    4. The Close Form button closes the form and saves the record in tblRental.

    Tom




    [quote name='HansV' post='784534' date='14-Jul-2009 12:21']I am sure that the situation is clear to you, but I'm afraid I don't understand the relationship between your description and the database that you posted. I am sorry about that.

    So I'll restrict myself to a few general remarks:
    • Create relationships with enforced referential integrity.
    • Foreign keys should have the same field size as primary keys, for example if the primary key is an AutoNumber field (i.e. a long integer), the foreign key should be a long integer, not a byte.
    • Remove unnecessary and duplicate indexes.
    • Set the Subdatasheet Name property of all tables to [none].
    • Take the customer query and the marketing query out of qryRentCalc0. The query should contain the CustomerID from tblRental, not from the customer query.
    • Don't save the record unconditionally. There is no point in trying to save the record if there is no CustomerID.
    [/quote]

Page 1 of 2 12 LastLast

Posting Permissions

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