Some data not saving to database table

[castle]

Im using access 2003 Vb code

I cant get the times (lst_StartTime and lst_EndTime) to save to the
database tables. Timetable no. and client no. do save, i've left out the
dayNo as it says needs related data so nulling it for now.



Option Compare Database
Option Explicit
Dim cn As ADODB.Connection
Dim rs_Timetable As ADODB.Recordset
Dim rs_Client As ADODB.Recordset
Dim rs_Cleaning As ADODB.Recordset



Private Sub btn_Add_Click()

With rs_Timetable

.AddNew ' Blank Record

!TimeTableNo = lbl_TimeTableNo.Caption

!ClientNo = lst_Client '.Column(1)

'!DayNo = Lst_Day.Column(1)

!DayNo = Null

!StartTime = Lst_StartTime.Value

!EndTime = lst_EndTime

!StaffNo = lst_Staff '.Column(1)

.Update

End With
End Sub



Private Sub cmd_exit_Click()
DoCmd.Close
End Sub



Private Sub Form_Load()
DoCmd.Maximize
Lbl_date.Caption = Date

Set cn = CurrentProject.Connection

Set rs_Timetable = New ADODB.Recordset
rs_Timetable.Open "Timetable", cn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect
rs_Timetable.Index = "PrimaryKey"


Set rs_Client = New ADODB.Recordset
rs_Client.Open "Client", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
rs_Client.Index = "PrimaryKey"

Set rs_Cleaning = New ADODB.Recordset
rs_Cleaning.Open "CleaningDays", cn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect
rs_Cleaning.Index = "PrimaryKey"

With rs_Timetable

If .EOF = True Then

lbl_TimeTableNo.Caption = 1000

Else

.MoveLast

lbl_TimeTableNo.Caption = !TimeTableNo + 1



End If

End With

Detail.Visible = False

lst_Client.SetFocus

End Sub



Private Sub Form_Timer()
lbl_time.Caption = Format(Now, "hh:mm:ss")
End Sub

Private Sub lst_Client_AfterUpdate()

Detail.Visible = True

rs_Client.Seek lst_Client, adSeekFirstEQ

rs_Cleaning.Seek rs_Client!ChargeNo, adSeekFirstEQ

lbl_Hours.Caption = rs_Cleaning!Noofhours

lbl_StartDate.Caption = rs_Client!StartDate

If rs_Cleaning!duration = "F" Then

lbl_Duration.Caption = "Fortnightly"

Else

lbl_Duration.Caption = "Monthly"

End If

Lst_Day.SetFocus

End Sub


Tried this but it don't work

!StartTime = format(lst_Start.value,"dd-mmm-yyyy hh:mms")

 

[Dale Fye]

Assuming that the StartTime and EndTime fields are dates. List boxes convert
data into text strings, so you probably need to explicitly type the value
from the listbox. Try:

IF Len(me.lst_StartTime & "") > 0 Then !StartTime = cdate(me.lst_StartTime)

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[castle]

No, it is time as in 17.30 etc, used only hh:mm but that did not work. I
added the list box items by using F4 and tying them in there.

did not try your way as its for date.

 

[Dale Fye]

What is the data type of the StartTime and EndTime fields in your table?
What is the Format of the field set to? Delete the format property from the
table and take a look at the values in that field. What do they look like.
Also, remove the formatting from the textbox on your form and see what the
values look like.

 

[castle]

Data type in table was set to accept text, it did not save so i changed it to
accept date/time and short time as in 17:00, that also did not work.

not sure how to;
Delete the format property from the
table and take a look at the values in that field. What do they look like.
Also, remove the formatting from the textbox on your form and see what the
values look like.

but will try that tomorrow, doing a night shift now.

Thanks

 

[castle]

1 What is the data type of the StartTime and EndTime fields in your table?
2 What is the Format of the field set to? Delete the format property from the
table and take a look at the values in that field. What do they look like.
3 Also, remove the formatting from the textbox on your form and see what the
values look like.

1 I set this for text and ran (1 to 5 (see code)) this did not work. Set it
to date/time and ran (1 to 5) and still did not work.

2 Also then changed format to show short time did not work either.

3 Tried but not sure what to do here. Or do you mean the list box as i only
have labels and listboxes on the form.


Private Sub btn_Add_Click()
With rs_Timetable
.AddNew ' Blank Record
!TimeTableNo = lbl_TimeTableNo.Caption
!ClientNo = lst_Client
!DayNo = Lst_Day.Column(0)

'!StartTime = Format(Lst_StartTime.Value, "hh:mm") '(1)
'!StartTime = Lst_StartTime.Value '(2)
'!StartTime = Lst_StartTime '(3)
'!StartTime = (Me.Lst_StartTime) '(4)
If Len(Me.Lst_StartTime & "") > 0 Then !StartTime =
CDate(Me.Lst_StartTime) '(5)


'!EndTime = lst_EndTime
!StaffNo = lst_Staff
.Update
End With

End sub

 

[Dale Fye]

I'm still trying to trouble-shoot. Personally, I would set the data types
of the StartTime and EndTime fields to Date/Time. What you set the format
property is immaterial, but it would be easiest to read if you set it to one
of the time formats.

What does the information displayed in your list box look like (all of the
columns, exactly as displayed, don't need every row, just a few)?

What is the query (SQL string) or the table (with fields in the sequence
they are displayed in the listbox)

What is the bound column in your list?

Dale

 

[castle]

The data is displayed as 09:00 etc as entered into the raw source like so-
09:00;09:30;10:00

The bound column has a 2 in it.

No query is used for the listbox.

I have placed a few screenshots into msn groups, my id is castle191.

Thanks

 

[castle]

The group can be found here

http://groups.msn.com/castle191

 

[Dale Fye]

It appears that your start and end time listbox are filled with their values
via a value list, that you have entered directly into the RowSource property
of the listbox.

If this is the case, then you need to make sure the following settings apply
to the listbox:
1. ColumnCount (Format tab): 1
2. BoundColumn (Data tab): 1
3. Multi-select (Other tab): None

If you only have one column of data, you need to make sure that both the
Column Count and Bound column both indicate 1 column. Additionally, since
list boxes can be setup for only 1 selection, or for multiple selections, if
it is set up for multiple selections, you cannot simply refer to the value of
the listbox, as it may have multiple values. In this case, you don't need to
be able to select multiple start times, so you should set the Multi-Select
property to None.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[castle]

I changed the bound column to 1 and that worked.

Thanks for your help!!