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

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
Kamran MahmoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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","")
0
Kamran MahmoodAuthor 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 :)
0
Kamran MahmoodAuthor Commented:
It is same like you entered first row, but on number of nights the rest of the rows will be added automatically ....
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
0
Gustav BrockCIOCommented:
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

0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
btw, you want to do this via ASP.NET or Access Form/ VBA ?
0
Kamran MahmoodAuthor Commented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.