Beacons' ADO Database Tutorial, question

[Terrain]

Followed the tutorial and it ran OK, I am trying to make a log book that will start with an empty database and have records entered each day. I am a complete novice, anyone out there who can help me create this.

[PilgrimPete]

I'm sure we'll all chip in...
...you'll need to make a start and then ask for help as you hit a problem.
And be as specific as possible in your requests.

How far have you got so far?

[Hack]

What will you be using as a front end? VB6? VB.NET? Something else?

[Terrain]

I will be using VB6, and I was hoping I could rearange the tutorial database to suit my needs. Is this possible.?

[si_the_geek]

Of course.. just set up the database as you want, and make changes to the appropriate parts of the program - such as putting the right table name in when the recordset is opened, and changing FillFields so that is uses the right field and control names.

[Terrain]

Thanks, I will give it a try, and see how I get on.

[Terrain]

I have removed the code from my post as I made a right mess of it.

[Terrain]

Please could someone tell me why I get the error "Invalid use of Null" when I run this code.
CODE] Public Sub fillfields()
If Not (rs.BOF = True Or rs.EOF = True) Then
Text1.Text = rs.Fields("Date")
Text2.Text = rs.Fields("Diving Contractor")
Text3.Text = rs.Fields("Contractors Address")
Text4.Text = rs.Fields("Type of dive")
Text5.Text = rs.Fields("Dive one depth")
Text6.Text = rs.Fields("Dive two depth")
Text7.Text = rs.Fields("Dive three depth")
Text8.Text = rs.Fields("Dive four depth")
Text9.Text = rs.Fields("Installation")
Text10.Text = rs.Fields("Vessel")
Text11.Text = rs.Fields("Dive Location")
Text12.Text = rs.Fields("Number of divers")
Text13.Text = rs.Fields("Decompression type")
Text14.Text = rs.Fields("Storage Depth")
Text15.Text = rs.Fields("Excursion Depth")
Text16.Text = rs.Fields("Number of bell runs")
Text17.Text = rs.Fields("No: divers")
Text18.Text = rs.Fields("Decompression Range")
Text19.Text = rs.Fields("Work Description")
Text20.Text = rs.Fields("Incidents and remarks")

Else
MsgBox "Either you are at the first record or the last.", vbExclamation, "cannot Move"
End If
End Sub[/CODE]

[GaryMazzone]

There is a field in the database with nothing in it (NULL data). You are trying to assign that NULL data to a textbox on your form. The easiest way to avoid this error is like this:

Text20.Text = rs.Fields("Incidents and remarks") & ""

Not the empty string ("") appended to the database field when assigning. Also a mistake made by beginers with databases is to place spaces in field names. Avoid this like the plague. You might be allowed to do this ini MS Access but most large RDMS do not and it will cause all sorts of pain and suffering working with the database.

[Terrain]

If I want the database to have no current records would I use this code for every field? and be able to add records.

[si_the_geek]

You use that code (the &"" ) for each field that can have no data in it.

The first line (the check of BOF and EOF) safely checks for no records - the rest of the code won't be used when there is no record.


Like Gary, I would also recommend changing the field names so they don't have spaces (eg: use DivingContractor), and also change the field name of Date to something else (as Date is a function, which will make your program go a bit weird if you aren't careful), perhaps DiveDate.

[Terrain]

Thank you, I will change the field names as advised, and enter the &"" code, and see what happens.

[Terrain]

Please could you tell me how, after adding a new record and updating it the the mdb, I can clear the data ready for next record.
I also want to include a horizontail scroll bar to scroll through the records. Is this very complicated??

 Private Sub cmdAdd_Click()
 With rs
  .AddNew
        .Fields("DiveDate") = Text1.Text
        .Fields("DivingContractor") = Text2.Text
        .Fields("ContractorsAddress") = Text3.Text
        .Fields("Typeofdive") = Text4.Text
        .Fields("Diveonedepth") = Text5.Text
        .Fields("Divetwodepth") = Text6.Text
        .Fields("Divethreedepth") = Text7.Text
        .Fields("Divefourdepth") = Text8.Text
        .Fields("Installation") = Text9.Text
        .Fields("Vessel") = Text10.Text
        .Fields("DiveLocation") = Text11.Text
        .Fields("Numberofdivers") = Text12.Text
        .Fields("Decompressiontype") = Text13.Text
        .Fields("StorageDepth") = Text14.Text
        .Fields("ExcursionDepth") = Text15.Text
        .Fields("Numberofbellruns") = Text16.Text
        .Fields("No:ofDivers") = Text17.Text
        .Fields("DecompressionRange") = Text18.Text
        .Fields("WorkDescription") = Text19.Text
        .Fields("Incidentsandremarks") = Text20.Text
    .Update
  End With
End Sub

[PilgrimPete]

There is no quick way to clear down the text boxes for the next record,
you'll have to do something like this:

Private Sub cmdAdd_Click()
 With rs
  .AddNew
        .Fields("DiveDate") = Text1.Text
        .Fields("DivingContractor") = Text2.Text
        .Fields("ContractorsAddress") = Text3.Text
        .Fields("Typeofdive") = Text4.Text
        .Fields("Diveonedepth") = Text5.Text
        .Fields("Divetwodepth") = Text6.Text
        .Fields("Divethreedepth") = Text7.Text
        .Fields("Divefourdepth") = Text8.Text
        .Fields("Installation") = Text9.Text
        .Fields("Vessel") = Text10.Text
        .Fields("DiveLocation") = Text11.Text
        .Fields("Numberofdivers") = Text12.Text
        .Fields("Decompressiontype") = Text13.Text
        .Fields("StorageDepth") = Text14.Text
        .Fields("ExcursionDepth") = Text15.Text
        .Fields("Numberofbellruns") = Text16.Text
        .Fields("No:ofDivers") = Text17.Text
        .Fields("DecompressionRange") = Text18.Text
        .Fields("WorkDescription") = Text19.Text
        .Fields("Incidentsandremarks") = Text20.Text
    .Update
  End With
 
  Text1.Text = vbNullString
  Text2.Text = vbNullString
'...etc...
  Text19.Text = vbNullString
  Text20.Text = vbNullString
End Sub

The scroll bar won't be too hard, but not very standard (IMO). I'd suggest using some derivative of the following buttons:
[First][Previous] {recnum} [Next][Last][New]
(They could be words or images.)

You can use the recordset "Move" methods - MoveNext, MovePrevious etc. - to navigate through the records as the user clicks on each relevant button.
For example,

Private Sub cmdNext_Click()
   rs.MoveNext
   fillfields
 End Sub

This is a simplistic example - you'll need to be careful about going past the end of the recordset, checking that there are records etc. but it should give you the idea.

[Terrain]

Thank you I will get on it.

[Terrain]

I have the database working now thank you all. Just one snag, I can't seem to unload the form. I have an unload code on the form but don't know how to access it from a control button. I need it to unload and go to my frmmenu.??

[CODE] Private Sub Form_Unload(Cancel As Integer)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
[/CODE

[brucevde]

Use the code Unload Me to exit a Form. For example, if you have a Button called cmdExit on the form.

Private Sub cmdExit_Click()
   Unload Me 'Causes the events Form_QueryUnload and Form_Unload to fire.
End Sub

[Terrain]

Thankyou works just as you said.
How can I stop my database saving blank records?

[PilgrimPete]

You could add validation to the cmdAdd_Click() event.
If Text1.Text has no value (or any other field(s) you may specify), then you could either do nothing, or preferably issue a message box to tell the user not to be silly!

Private Function IsValidRecord() as Boolean
   IsValidRecord = True

   If Text1.Text = vbNullString Then
      IsValidRecord = False
      MessageBox "Put some data in silly!"
   End if
End Function

[Terrain]

Do I enter this code inside the cmdAdd SUB??

[PilgrimPete]

Yep. Something like this:

Private Sub cmdAdd_Click()
 If IsValidRecord()  = true then
   With rs
    .AddNew
....
End Sub

[Terrain]

Thanks
[RESOLVED]