Populating Controls for Empty Value in Recordset 
Author Message
 Populating Controls for Empty Value in Recordset

How can I populate controls on this form when the recordset may have a null
or empty value? For instance the code below populates several controls on a
form but will display a "type mismatch" error if social security is empty. I
just would like it to display an empty control. Any advice would be
appreicated:

Private Sub Form_Activate()

Dim rs As ADOCE.Recordset
Set rs = CreateObject("adoce.recordset.3.1")
Dim SQL As String

Select Case strTripState
Case "Edit"
SQL = "SELECT * FROM tblCE WHERE REMStatID = '" & strTrip & "'"
If rs.State = adStateOpen Then
rs.Close
End If

rs.Open SQL, strDBPath, adOpenKeyset, adLockOptimistic

Me.txtSSN = rs("SSN")
Me.txtFirstName = rs("First Name")
Me.txtLastName = rs("Last Name")
Me.txtDOB = rs("DOB")
Me.txtID = rs("REMStatID")
Me.txtAddress = rs("Address")
Me.txtCity = rs("City")
Me.txtState = rs("State")
Me.txtZip = rs("Zip")

rs.Close
Set rs = Nothing

Case "Add"
ClearForm (frmMain) 'Clear the Main Form
Me.txtID = CStr(CDbl(Now())) 'Generate a unique Index
Me.txtDate = Date 'Sets to current date
strTrip = Me.txtID 'Set the active Tripsheet Number

End Select

End Sub

John D Kostenbader
Client Engineer
Med-Media, Inc.



Sat, 11 Sep 2004 00:04:50 GMT  
 Populating Controls for Empty Value in Recordset
Here is the routine I wrote to handle this issue:

in your example, wrap each line like this:

Me.txtAddress =CheckForNull( rs("Address"))

Public Function CheckForNull(vColumn As ADODB.Field)
   'replace nulls with a predetermined default value
   Dim vDefaultVal As Variant

   If IsNull(vColumn) Then
      Select Case vColumn.Type
         Case adChar, adVarChar, adDate, adDBDate, adDBTime, adDBTimeStamp
            vDefaultVal = ""
         Case adDouble, adCurrency, adDecimal, adInteger, adNumeric,
adSingle, adSmallInt, adTinyInt
            vDefaultVal = 0
         Case Else
            'try string anyway
            vDefaultVal = ""
      End Select
      CheckForNull = vDefaultVal
   Else
      CheckForNull = vColumn
   End If

End Function



Quote:
> How can I populate controls on this form when the recordset may have a
null
> or empty value? For instance the code below populates several controls on
a
> form but will display a "type mismatch" error if social security is empty.
I
> just would like it to display an empty control. Any advice would be
> appreicated:

> Private Sub Form_Activate()

> Dim rs As ADOCE.Recordset
> Set rs = CreateObject("adoce.recordset.3.1")
> Dim SQL As String

> Select Case strTripState
> Case "Edit"
> SQL = "SELECT * FROM tblCE WHERE REMStatID = '" & strTrip & "'"
> If rs.State = adStateOpen Then
> rs.Close
> End If

> rs.Open SQL, strDBPath, adOpenKeyset, adLockOptimistic

> Me.txtSSN = rs("SSN")
> Me.txtFirstName = rs("First Name")
> Me.txtLastName = rs("Last Name")
> Me.txtDOB = rs("DOB")
> Me.txtID = rs("REMStatID")
> Me.txtAddress = rs("Address")
> Me.txtCity = rs("City")
> Me.txtState = rs("State")
> Me.txtZip = rs("Zip")

> rs.Close
> Set rs = Nothing

> Case "Add"
> ClearForm (frmMain) 'Clear the Main Form
> Me.txtID = CStr(CDbl(Now())) 'Generate a unique Index
> Me.txtDate = Date 'Sets to current date
> strTrip = Me.txtID 'Set the active Tripsheet Number

> End Select

> End Sub

> John D Kostenbader
> Client Engineer
> Med-Media, Inc.



Sat, 11 Sep 2004 00:41:05 GMT  
 Populating Controls for Empty Value in Recordset
Richard...you are a god send...thanks so much. I learned a great deal from
you example. I'm going to apply similar logic to the opposite situation
where the control may be empty....

Thanks again

John Kostenbader


Quote:
> Here is the routine I wrote to handle this issue:

> in your example, wrap each line like this:

> Me.txtAddress =CheckForNull( rs("Address"))

> Public Function CheckForNull(vColumn As ADODB.Field)
>    'replace nulls with a predetermined default value
>    Dim vDefaultVal As Variant

>    If IsNull(vColumn) Then
>       Select Case vColumn.Type
>          Case adChar, adVarChar, adDate, adDBDate, adDBTime, adDBTimeStamp
>             vDefaultVal = ""
>          Case adDouble, adCurrency, adDecimal, adInteger, adNumeric,
> adSingle, adSmallInt, adTinyInt
>             vDefaultVal = 0
>          Case Else
>             'try string anyway
>             vDefaultVal = ""
>       End Select
>       CheckForNull = vDefaultVal
>    Else
>       CheckForNull = vColumn
>    End If

> End Function



> > How can I populate controls on this form when the recordset may have a
> null
> > or empty value? For instance the code below populates several controls
on
> a
> > form but will display a "type mismatch" error if social security is
empty.
> I
> > just would like it to display an empty control. Any advice would be
> > appreicated:

> > Private Sub Form_Activate()

> > Dim rs As ADOCE.Recordset
> > Set rs = CreateObject("adoce.recordset.3.1")
> > Dim SQL As String

> > Select Case strTripState
> > Case "Edit"
> > SQL = "SELECT * FROM tblCE WHERE REMStatID = '" & strTrip & "'"
> > If rs.State = adStateOpen Then
> > rs.Close
> > End If

> > rs.Open SQL, strDBPath, adOpenKeyset, adLockOptimistic

> > Me.txtSSN = rs("SSN")
> > Me.txtFirstName = rs("First Name")
> > Me.txtLastName = rs("Last Name")
> > Me.txtDOB = rs("DOB")
> > Me.txtID = rs("REMStatID")
> > Me.txtAddress = rs("Address")
> > Me.txtCity = rs("City")
> > Me.txtState = rs("State")
> > Me.txtZip = rs("Zip")

> > rs.Close
> > Set rs = Nothing

> > Case "Add"
> > ClearForm (frmMain) 'Clear the Main Form
> > Me.txtID = CStr(CDbl(Now())) 'Generate a unique Index
> > Me.txtDate = Date 'Sets to current date
> > strTrip = Me.txtID 'Set the active Tripsheet Number

> > End Select

> > End Sub

> > John D Kostenbader
> > Client Engineer
> > Med-Media, Inc.



Sat, 11 Sep 2004 01:09:53 GMT  
 Populating Controls for Empty Value in Recordset
Ok John, glad to help - I wrote that several years ago and I use it in every
DB app.



Quote:
> Richard...you are a god send...thanks so much. I learned a great deal from
> you example. I'm going to apply similar logic to the opposite situation
> where the control may be empty....

> Thanks again

> John Kostenbader



> > Here is the routine I wrote to handle this issue:

> > in your example, wrap each line like this:

> > Me.txtAddress =CheckForNull( rs("Address"))

> > Public Function CheckForNull(vColumn As ADODB.Field)
> >    'replace nulls with a predetermined default value
> >    Dim vDefaultVal As Variant

> >    If IsNull(vColumn) Then
> >       Select Case vColumn.Type
> >          Case adChar, adVarChar, adDate, adDBDate, adDBTime,
adDBTimeStamp
> >             vDefaultVal = ""
> >          Case adDouble, adCurrency, adDecimal, adInteger, adNumeric,
> > adSingle, adSmallInt, adTinyInt
> >             vDefaultVal = 0
> >          Case Else
> >             'try string anyway
> >             vDefaultVal = ""
> >       End Select
> >       CheckForNull = vDefaultVal
> >    Else
> >       CheckForNull = vColumn
> >    End If

> > End Function



> > > How can I populate controls on this form when the recordset may have a
> > null
> > > or empty value? For instance the code below populates several controls
> on
> > a
> > > form but will display a "type mismatch" error if social security is
> empty.
> > I
> > > just would like it to display an empty control. Any advice would be
> > > appreicated:

> > > Private Sub Form_Activate()

> > > Dim rs As ADOCE.Recordset
> > > Set rs = CreateObject("adoce.recordset.3.1")
> > > Dim SQL As String

> > > Select Case strTripState
> > > Case "Edit"
> > > SQL = "SELECT * FROM tblCE WHERE REMStatID = '" & strTrip & "'"
> > > If rs.State = adStateOpen Then
> > > rs.Close
> > > End If

> > > rs.Open SQL, strDBPath, adOpenKeyset, adLockOptimistic

> > > Me.txtSSN = rs("SSN")
> > > Me.txtFirstName = rs("First Name")
> > > Me.txtLastName = rs("Last Name")
> > > Me.txtDOB = rs("DOB")
> > > Me.txtID = rs("REMStatID")
> > > Me.txtAddress = rs("Address")
> > > Me.txtCity = rs("City")
> > > Me.txtState = rs("State")
> > > Me.txtZip = rs("Zip")

> > > rs.Close
> > > Set rs = Nothing

> > > Case "Add"
> > > ClearForm (frmMain) 'Clear the Main Form
> > > Me.txtID = CStr(CDbl(Now())) 'Generate a unique Index
> > > Me.txtDate = Date 'Sets to current date
> > > strTrip = Me.txtID 'Set the active Tripsheet Number

> > > End Select

> > > End Sub

> > > John D Kostenbader
> > > Client Engineer
> > > Med-Media, Inc.



Sat, 11 Sep 2004 01:22:36 GMT  
 Populating Controls for Empty Value in Recordset
For text fields it's much easier to just append an empty string to the field
like this:

txtAddress = rs("Address").Value & ""

-Chris


Quote:
> Here is the routine I wrote to handle this issue:

> in your example, wrap each line like this:

> Me.txtAddress =CheckForNull( rs("Address"))

> Public Function CheckForNull(vColumn As ADODB.Field)
>    'replace nulls with a predetermined default value
>    Dim vDefaultVal As Variant

>    If IsNull(vColumn) Then
>       Select Case vColumn.Type
>          Case adChar, adVarChar, adDate, adDBDate, adDBTime, adDBTimeStamp
>             vDefaultVal = ""
>          Case adDouble, adCurrency, adDecimal, adInteger, adNumeric,
> adSingle, adSmallInt, adTinyInt
>             vDefaultVal = 0
>          Case Else
>             'try string anyway
>             vDefaultVal = ""
>       End Select
>       CheckForNull = vDefaultVal
>    Else
>       CheckForNull = vColumn
>    End If

> End Function



> > How can I populate controls on this form when the recordset may have a
> null
> > or empty value? For instance the code below populates several controls
on
> a
> > form but will display a "type mismatch" error if social security is
empty.
> I
> > just would like it to display an empty control. Any advice would be
> > appreicated:

> > Private Sub Form_Activate()

> > Dim rs As ADOCE.Recordset
> > Set rs = CreateObject("adoce.recordset.3.1")
> > Dim SQL As String

> > Select Case strTripState
> > Case "Edit"
> > SQL = "SELECT * FROM tblCE WHERE REMStatID = '" & strTrip & "'"
> > If rs.State = adStateOpen Then
> > rs.Close
> > End If

> > rs.Open SQL, strDBPath, adOpenKeyset, adLockOptimistic

> > Me.txtSSN = rs("SSN")
> > Me.txtFirstName = rs("First Name")
> > Me.txtLastName = rs("Last Name")
> > Me.txtDOB = rs("DOB")
> > Me.txtID = rs("REMStatID")
> > Me.txtAddress = rs("Address")
> > Me.txtCity = rs("City")
> > Me.txtState = rs("State")
> > Me.txtZip = rs("Zip")

> > rs.Close
> > Set rs = Nothing

> > Case "Add"
> > ClearForm (frmMain) 'Clear the Main Form
> > Me.txtID = CStr(CDbl(Now())) 'Generate a unique Index
> > Me.txtDate = Date 'Sets to current date
> > strTrip = Me.txtID 'Set the active Tripsheet Number

> > End Select

> > End Sub

> > John D Kostenbader
> > Client Engineer
> > Med-Media, Inc.



Sat, 11 Sep 2004 01:54:51 GMT  
 Populating Controls for Empty Value in Recordset
Way back when, I used to assign values to textboxes like this.

Text1.Text = adoRs.Fields("Field1").Value & ""

Worked just dandy

Rick


Quote:
> How can I populate controls on this form when the recordset may have a
null
> or empty value? For instance the code below populates several controls on
a
> form but will display a "type mismatch" error if social security is empty.
I
> just would like it to display an empty control. Any advice would be
> appreicated:

> Private Sub Form_Activate()

> Dim rs As ADOCE.Recordset
> Set rs = CreateObject("adoce.recordset.3.1")
> Dim SQL As String

> Select Case strTripState
> Case "Edit"
> SQL = "SELECT * FROM tblCE WHERE REMStatID = '" & strTrip & "'"
> If rs.State = adStateOpen Then
> rs.Close
> End If

> rs.Open SQL, strDBPath, adOpenKeyset, adLockOptimistic

> Me.txtSSN = rs("SSN")
> Me.txtFirstName = rs("First Name")
> Me.txtLastName = rs("Last Name")
> Me.txtDOB = rs("DOB")
> Me.txtID = rs("REMStatID")
> Me.txtAddress = rs("Address")
> Me.txtCity = rs("City")
> Me.txtState = rs("State")
> Me.txtZip = rs("Zip")

> rs.Close
> Set rs = Nothing

> Case "Add"
> ClearForm (frmMain) 'Clear the Main Form
> Me.txtID = CStr(CDbl(Now())) 'Generate a unique Index
> Me.txtDate = Date 'Sets to current date
> strTrip = Me.txtID 'Set the active Tripsheet Number

> End Select

> End Sub

> John D Kostenbader
> Client Engineer
> Med-Media, Inc.



Sat, 11 Sep 2004 02:54:23 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Populate empty table using Recordset

2. Recordset BOF adoEOF indicate that it is empty but it is not empty

3. empty recordset cannot be bound to control

4. Can DBGrid be populated with the Recordset and not Data Control

5. Populating Listview control with recordset

6. Populate a flexigrid control with a recordset

7. Can DBGrid be populated with the Recordset and not Data Control

8. How to Empty a Populated Datagrid?

9. filling an empty treeview by using a populated one

10. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

11. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

12. adodb connection to excel: recordset.fields(i).value=null for fields with numeric value

 

 
Powered by phpBB® Forum Software