flexgrid with Access

[engnouna]

Hi
I have a MSFlexgrid inside my VB6 form.The backend is Access 2003.This flexgrid contains 144 rows and 1 column Room1:The Flexgrid looks like:
Intervals Room1
06.00 to 06.10
06.10 to 06.20
06.20 to 06.30
06.30 to 06.40
...and so on covering the 24 hours

If a client wants to book the Room1 between 06.00 to 06.30 then I add a record to a table "Book" in which I enter all the information about the client and I add the Intervals' ID to a table "Intervallist" that has 3 fields:
ID-Autonumber
RId-The record ID of the record just added(containing the informations about the client)
IntervalID-The intervals ID(e.g if the user wants to book the room between 06.00 to 06.30 then suppose that 06.00 to 06.10 has an ID of 7,06.10 to 06.20 has an ID of 8 and 06.20 to 06.30 has an ID of 9 then if Rid=24
then I should add to intervallist
Autonumber 24 7
Autonumber 24 8
Autonumber 24 9

Suppose that the user changes his mind and He want to book the room between 06.00 to 06.50 what's the best way to edit the intervallist table?
My method was to delete all the records in Intervallist that has a RID=24 and then reenter the new intervals' ID with their corresponding RID,but this method causes to me a lot of Problems.

Can anyone help me because it's extremely urgent
thanks in advance

[si_the_geek]

I would change the table design.. unless all the possible intervals are in the database (which would be a waste of space for unused ones), it would make more sense to simply store the Start and End time of each booking.

That way you only use one record per booking (which is all that is really needed), so is much easier to change.

As far as I see it, you need a table which contains Room info, and a table which contains Booking info - which would contain a reference to Room, and the start & end times (with date). Your interface wouldn't need to change (but could if you wanted to).


If you want to correct what you have, we'll obviously need to see the code to be able to say what you are doing wrong.

[RobDog888]

By using a subform with a table displayed in data entry view you can duplicate a flex grid but with more flexibility in Access by using an exact version of a table view.

[engnouna]

RobDog888 I didn't catch ur proposition
Well my code is
suppose that the starting date is 06.00
sint=06.00
BInterval contains all the intervals with their ID
ID Interval
1 06.00 to 06.10
2 06.10 to 06.20
3 06.20 to 06.30
4 06.30 to 06.40

rs.Open "Select * from BInterval where Left(BInterval,5) = " & sint, cn, adOpenKeyset, adLockPessimistic
nc = rs.Fields(0) 'so in this case nc=1
rs.Close
 
int contains the number of intervals,e.g If the user wants to book the room between 06.00 to 06.20 so we have 2 intervals(06.00 to 06.10 and 06.10 to 06.20) so int=2
 
rs5.open"Select * from intervallist",cn,adopenKeyset,adlockpessimistic
For i = 0 To int
rs5.AddNew
rs5.Fields("BID") = nar 'nar is the ID of the record containing the client information
rs5.Fields("BIntID") = nc + i
Next i
End If

What's wrong with this?
thanks

[RobDog888]

Like this.

[engnouna]

Si-the-Geek my method causes to me a lot of problems so I decided to adopt your method,so when the user chooses to book the room from 06.10 to 07.30 and 06.10's ID=2 and 07.30's ID=9 then I will send 2 fields into my table
Starting time=2 (2 is the ID of 06.10)
Ending time=9(9 is the ID of 07.30)

After that the Flexgrid should display the new entered book(from 06.10 to 07.30)
The Flexgrid look like
Interval Room
06.00 to 06.10
06.10 to 06.20
06.20 to 06.30
06.30 to 06.40
06.40 to 06.50
06.50 to 07.00
07.00 to 07.10
07.10 to 07.20
07.20 to 07.30

So the flexgrid should highlight in red all the following cells:06.10 to 06.20
06.20 to 06.3, 06.30 to 06.40, 06.40 to 06.50, 06.50 to 07.00,07.00 to 07.10,07.10 to 07.20,07.20 to 07.30

How can I do it?
thanks in advance

[ganeshmoorthy]

For i = 1 To 10 'The row numbers from which row to which row to be highlighted
        MSFlexGrid1.Col = 1 'Your column which has got the interval
        MSFlexGrid1.Row = i 
        MSFlexGrid1.CellForeColor = vbRed 'to change the font color in red
    Next

[engnouna]

ganeshmoorthy I want to highlight cells have a rowdata between 2 different values
thanks

[si_the_geek]

Then simply loop thru the rows, and colour them if the rowdata is apt.

eg:

With MSFlexGrid1
    For i = .FixedRows To .Rows -1
      If .RowData(i) >= [u]LowVal[/u] And .RowData(i) <= [u]HighVal[/u] Then
        .Col = 1 
        .Row = i 
        .CellForeColor = vbRed
      End If
    Next
  End With

[engnouna]

Thanks Si_the_Geek