Need Experts Opinion, using 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 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.

Kamran MahmoodAsked:
Who is Participating?
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","")
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 :)
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 ....
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Ryan ChongSoftware Team LeadCommented:
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.
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!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

Open in new window

Ryan ChongSoftware Team LeadCommented:
btw, you want to do this via ASP.NET or Access Form/ VBA ?
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()
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

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 ChongSoftware Team LeadCommented:
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.
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

From novice to tech pro — start learning today.