msflexgrid and access database problem

vb6 , msflexgrid , ms access , vb msaccess Liverpool, United Kingdom
  • 9 years ago

    I am writing a Rota program and I came up to a problem. I currently have a database with tblmain (1 to many) tblholiday. in tblmain there is employee details & shifts assigned. in tblHoliday there are holiday Details:

    1. HolidayStart (as date) 2.HolidayEnd (as Date) 3.Days_taken (text) 4.AbsenceID (text)

    in my program I use INSERT function to insert the records on specific employee into tblHoliday.

    in Form_load i use loops to put all names and shitfs to put it on msflexgrid.

    problem:

    msflexgrid has dates on second row according to (monday, tuesday etc) I want to compare the dates with the ones on the database and put HOL instead of a 10.00-14.00 if the dates are the same. another tricky thing when i store the holidays it stores it as follows ie. 27/07/2009 into Holidaystart, and 29/07/2009 and then how many days taken into the daystaken.

    this loads data onto every column of my msflexgrid(grdRota)

    rsMyRS.MoveFirst For c = 2 To rsMyRS.RecordCount + 1 If Len(rsMyRS!fullname) > intStr Then intStr = Len(rsMyRS!fullname) grdRota.TextMatrix(c, 0) = rsMyRS!fullname rsMyRS.MoveNext Next c grdRota.ColWidth(0) = intStr * 160

    this inserts the holiday

    conConnection.Open With cmdCommand .ActiveConnection = conConnection .CommandText = "SELECT * FROM tblmain WHERE fullname = '" & fullname & "';" .CommandType = adCmdText End With

    With rstRecordset .CursorType = adOpenStatic .CursorLocation = adUseClient .LockType = adLockOptimistic .Open cmdCommand End With

    With cmdCommand .ActiveConnection = conConnection .CommandText = "INSERT into tblHoliday (AbsenceID, Holidays, Holidays1, DaysTaken) Values ('" & AbsenceID & "', '" & Holidays & "', '" & Holidays1 & "', '" & intDay & "')"

    .CommandType = adCmdText .Execute End With

    MsgBox "The record has been updated...", vbInformation, "Success!"

    I know this doesnt sound very clear to whoever reads it but to explain what i want is quite difficult. Thanks to everyone who contributes to solve this problem

  • 9 years ago

    I am writing a Rota program and I came up to a problem. I currently have a database with tblmain (1 to many) tblholiday. in tblmain there is employee details & shifts assigned. in tblHoliday there are holiday Details:

    1. HolidayStart (as date) 2.HolidayEnd (as Date) 3.Days_taken (text) 4.AbsenceID (text)

    in my program I use INSERT function to insert the records on specific employee into tblHoliday.

    in Form_load i use loops to put all names and shitfs to put it on msflexgrid.

    problem:

    msflexgrid has dates on second row according to (monday, tuesday etc) I want to compare the dates with the ones on the database and put HOL instead of a 10.00-14.00 if the dates are the same. another tricky thing when i store the holidays it stores it as follows ie. 27/07/2009 into Holidaystart, and 29/07/2009 and then how many days taken into the daystaken.

    this loads data onto every column of my msflexgrid(grdRota)

    rsMyRS.MoveFirst For c = 2 To rsMyRS.RecordCount + 1 If Len(rsMyRS!fullname) > intStr Then intStr = Len(rsMyRS!fullname) grdRota.TextMatrix(c, 0) = rsMyRS!fullname rsMyRS.MoveNext Next c grdRota.ColWidth(0) = intStr * 160

    this inserts the holiday

    conConnection.Open With cmdCommand .ActiveConnection = conConnection .CommandText = "SELECT * FROM tblmain WHERE fullname = '" & fullname & "';" .CommandType = adCmdText End With

    With rstRecordset .CursorType = adOpenStatic .CursorLocation = adUseClient .LockType = adLockOptimistic .Open cmdCommand End With

    With cmdCommand .ActiveConnection = conConnection .CommandText = "INSERT into tblHoliday (AbsenceID, Holidays, Holidays1, DaysTaken) Values ('" & AbsenceID & "', '" & Holidays & "', '" & Holidays1 & "', '" & intDay & "')"

    .CommandType = adCmdText .Execute End With

    MsgBox "The record has been updated...", vbInformation, "Success!"

    I know this doesnt sound very clear to whoever reads it but to explain what i want is quite difficult. Thanks to everyone who contributes to solve this problem

  • 9 years ago

    , must declare recordset variables 'must declare strSQL as string

    'Initialize Payment Grid Set = New ADODB.Recordset 'You can join two tables together with select strSQL = "select * from PayDetail where Cheqid = " & vRecordID .Open strSQL, cnSQL, adOpenStatic, adLockOptimistic Set .DataSource = rsname 'you can use a with block to format the flexgrid With .ColWidth(0) = 0 .ColWidth(1) = 1300: .ColAlignment(1) = flexAlignLeftCenter .ColWidth(2) = 1000: .ColWidth(3) = 1300: .ColFormat(3) = "mmm-d-yyyy"

        End With
    

    'Once user makes changes on the grid you can use an update statement to save changes to the record set .UpdateBatch adAffectCurrent

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Java is to JavaScript what Car is to Carpet.” - Chris Heilmann