Need Experts Opinion, using Asp.net VB & MS Access Arrival on - departure dates

Kamran Mahmood
Kamran Mahmood used Ask the Experts™
on
Dear Experts,
I hope i will get the answers of my question by today ..
I am working on Hospitality Management Software, of which 70% is done only Billing Section's few part left .. and thats where i am stuck. I am using Asp.net VB & MS Access as back-end.
My Requirement i would like to take a value when a NEW RESERVATION will be made "Arrival on" to "Departure"
Right now i just can save 2 values which is Arrival date & Departure date in database but i want something like this as below ...
so what i need i am giving you idea in DB actually how it goes ....

id    confirmid   name     arrivalon     departure         currentdate
1      1111           ABC       01/3/2018   01/05/2018       01/3/2018
2      1111           ABC       01/3/2018   01/05/2018       01/4/2018
3     1111           ABC       01/3/2018   01/05/2018       01/5/2018   ------> if this equals departure date ABC  CHECK OUT
4      2222           XYZ        01/3/2018   01/04/2018       01/3/2018
5      2222           XYZ        01/3/2018   01/04/2018       01/4/2018    ------> if this equals departure date XYZ CHECK OUT

so my problem is i just can save arrival on & departure first row which is id 1, the remaining id 2 & & 3 how can i save,
Similarly id 4 is saved when i make a new reservation the id 5 how can i save.

PLS HELP ... I NEED MY PROJECT TO FINISH IT BY A NEXT WEEK, THANKS IN ADVANCE
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It is not very clear where and how you display this, but the expression to use will be similar to:

    =IIf([Departure]=Date(),"CHECK OUT","")

Author

Commented:
Dear Gustav, ok i will clear the case again, the problem is not the criteria, the problem is
when you save reservation it only saves arrival date and departure date as i input in datepicker, the real issue is how would i calculate the in between days and save in database, as you can see in DB diagram, i can save the first record, but i would like to save 2nd & 3rd Record as well in single submit click. I hope you can get what i am looking for. thanks :)

Author

Commented:
It is same like you entered first row, but on number of nights the rest of the rows will be added automatically ....
Ryan ChongSoftware Team Lead

Commented:
It is same like you entered first row, but on number of nights the rest of the rows will be added automatically ....
a looping in the on Click event of your "Save" button should allow you to insert multiple records, the difference of days will determine how many records to be inserted in the loop.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use the AfterUpdate event of the record:

Dim rs As DAO.Recordset

Dim Day As Integer
Dim Days As Integer

Set rs = Me.RecordsetClone
Days = DateDiff("d", [Arrival], [Departure])

For Day = 2 To Days
    rs.AddNew
        rs!ConfirmId.Value = Me!ConfirmId.Value
        rs!Name.Value = Me!Name.Value
        rs!Arrival.Value = Me!Arrival.Value
        rs!Departure.Value = Me!Departure.Value
        rs!CurrentDate.Value = DateAdd("d", Day - 1, Me!CurrentDate.Value
    rs.Update
Next
rs.Close

Open in new window

Ryan ChongSoftware Team Lead

Commented:
btw, you want to do this via ASP.NET or Access Form/ VBA ?
Thanks A lot, but i found a solution by using

  Dim i As Integer
        For i = 0 To Me.txtBoxNoN.Text.ToString()
            Me.myAccessConnection.Open()
Using command As New OleDbCommand    ---> This is key to success to run a insert command for each record you want to save

.... Here my insert into will go & using loop

end using
   next
Ryan ChongSoftware Team Lead

Commented:
so it seems that you're using ASP.NET and not Access VBA. hence, i would suggest to accept your own comment as the solution instead of accepting Gustav's suggestion as the answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial