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?
Kamran MahmoodConnect With a Mentor Author 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
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 :)
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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 ....
Ryan ChongCommented:
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 ChongCommented:
btw, you want to do this via ASP.NET or Access Form/ VBA ?
Ryan ChongCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.