Visual Basic :: How To Tell A Field In A Recordset Is Empty

Recordset Field Value Empty When Using CursorLocation = AdUseServer
Greetings!

Here's my problem:

I have a recordset that ABSOLUTELY needs to have server side cursor.
If I do something like:

Code:
Dim oConn as ADODB.Connection
Dim oRs as ADODB.Recordset
Dim sSql as String
Dim sValue as String

sSql = "SELECT * FROM Item WHERE pkIdItem = 1"

Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseServer
Set oRs = oConn.Execute(SQL)
While Not oRs.EOF
sValue = oRs("The75thField")
'// Here, I grab all the 75 fields from the recordset, or do whatever I need
'// to do with the values.
Wend
Set oRs = Nothing
Set oConn = Nothing
I have lots of fields that are "Empty" (not null or "").

Does anybody have any idea how to fix this problem? This particular behavior always happens when I am using tables that have lots of fields inside. Usually, I just fix it by setting the Cursor Location to "adUseClient", but for a reason of "memory managing" and speed of my application (kind of...), I absolutely need to use a server side cursor.

Thanks in advance!

SQL - Select All Records From Table Where Field Is Empty / Not Empty ?
I have a problem here in that I dont know how to execute this SQL query as I am not sure of the correct syntax...

I am trying to select all records from a recordset (populated by a table), where a particular field is empty...

So I guessed...

db.Execute("SELECT * FROM CUSTOMER ORDERBY CustomerID WHERE CustTelephone = "" ")

but its not working...

Similiarly, I need a query that returns all records where a certain field is filled, I have no idea how to do this one...

Guessed at...

db.Execute("SELECT * FROM CUSTOMER ORDERBY CustomerID WHERE CustOrder = "'(value)'" ")

(where '(value)' would mean any value at all)

funny enough its not working either

Can anyone please help ?

Thanks

Urgent : Create An Empty Recordset And Validate A New Empty Array
Hi everyone,

I'm new in this kind of development.

I need to create an empty recordset, just to create an empty shell with a structure (not a data type). So, I'm trying to 1. Open it 2. Adding new row then with the addnew method.

Also, I need to validate a dynamic array before adding records to the recordset.

Thanks everyone.

Empty Line In Datagrid With An Empty Recordset
I've a datagrid of which I connect the datasource to my recordset. But when the result of this recordset is empty, I get an empty line in my datagrid. Then if I try to display a new recordset, I get the error :

"Either BOF or EOF is True, or the current record has been deleted; the operation requested by the application requires a current record."

So the system want to update my data, but the data doesn't exists.

Does somebody know how I can prevent getting an empty line with an empty recordset ?

Thanks!!!

Greetz,Joris

Empty Field?
Hi

I have a Data Grid Control that is connected via ADODC to a Microsoft Access DB. I want the user to be able to add, modify, or delete records on the grid. This all works fine, except when the form opens up, the first field of the first record is blank with the cursor blinking there. For some reason it sticks the cursor there and erases what was previously in that field.

Does anyone know how to remedy this?

Thanks!

Empty Field
What is the value of an empty field? I tried testing a recordset for empty fields like this...

Code:
if rsTabSearch.Fields("FileProperties") = "" then
'code here
end if
if rsTabSearch.Fields("FileProperties") = Null then
'code here
end if

but none are working... Any thoughts on this?

Fill A Dbf Or Access Table Field With A Recordset Field
Hello,
I need to fill out a dbf field with values from a recordset I get through a query.
The target dbf has a "variable" field which have to be filled with values from a recordset.
Any help will be appreciated.
Hayk

How To Figure Out The Empty Field?
I have following code to read one text filed(atafromemail.txt), and change the pattern to another text file(SwitchPattern.tex). finally, convert to excel
datafroEmail.txt:
Total Amount: $8.95 USD
Currency: U.S. Dollars
Transaction ID: 32238683KU050193G
Item Number: 30524117856
Buyer: Betsi Coleman
Buyer's User ID: betsitwo
Message: bla ...
betsitwo's CONFIRMED Address: 1234 St.
.
.

SwitchPattern.txt:

"$8.95 USD","U.S. Dollars","32238683KU050193G","30524117856","Betsi Coleman","betsitwo", "bla ...", "1234 St."

My problem is sometimes data from email no the Message line at all.
This will cost problem in switchPattern file like the address line will move foward to Message field and so on. If there were more than one record, the whole thing will mess up. Anybody help?
<vbt>
Private Sub TextSwitch()
Dim txtAmount, txtCurr, txtTranID, _
txtNum, txtBuyer, txtUserID, txtmessage, txtAddress, xR As String
Dim i, j
Open App.Path & "SwitchPattern.txt" For Output As #1

Open App.Path & "DataFromEmail.txt" For Input Access Read As #6

Do While Not EOF(6)
Line Input #6, xR 'Read a line
If Left(LTrim(xR), 14) = "Total Amount: " Then
txtAmount = Right(xR, Len(xR) - 14)
Write #1, txtAmount,
End If

If Left(LTrim(xR), 10) = "Currency: " Then
txtCurr = Right(xR, Len(xR) - 10)
Write #1, txtCurr,
End If

If Left(LTrim(xR), 16) = "Transaction ID: " Then
txtTranID = Right(xR, Len(xR) - 16)
Write #1, txtTranID,
End If

If Left(LTrim(xR), 13) = "Item Number: " Then
txtNum = Right(xR, Len(xR) - 13)
Write #1, txtNum,
End If

If Left(LTrim(xR), 7) = "Buyer: " Then
txtBuyer = Right(xR, Len(xR) - 7)
Write #1, txtBuyer,
End If

If Left(LTrim(xR), 17) = "Buyer's User ID: " Then
txtUserID = Right(xR, Len(xR) - 17)
Write #1, txtUserID,
End If

If Left(LTrim(xR), 9) = "Message: " Then
txtmessage = Right(xR, Len(xR) - 9)
Write #1, txtmessage,
End If

j = InStr(1, xR, ":", 1)
i = InStr(1, xR, "CONFIRMED Address:", 1)
If i <> 0 Then
If Mid(LTrim(xR), i, 18) = "CONFIRMED Address:" Then
txtAddress = Right(xR, Len(xR) - j)
Write #1, txtAddress
End If
End If

Loop
Close #6 'Close
Close #1

End Sub</vbt>

Thank you!

Fang

Filter For Empty Field In ADO
I want to filter a Recordset for two cases of a date field: either empty or not.

On other fields, I am using:
rs.Filter = "FieldName = Whatever" which works fine.

How do I specify FieldName = "" or Fieldname <> ""?

As you can probably tell, I am new at this :-)

Thanks!

How To Look For An Empty Or Null Field ?
Hi,

I´d like to:
Connection.execute ("SELECT * FROM TABLE WHERE blabla = Null ").

Why doesn´t it work ?? It´s a DateField.

Any ideas ??

How To Proof If A Field Is Empty ??
Hellllooo,

i want to proof if a table field is empty. I tried this :

If !State = "" Then
Text3(2).Text = "No Content !"
Else
Text3(2).Text = !State
End If

Does anybody know a working solution ??
Mine causes error: illegal use of null. Means that !State is empty and Text3(2).Text doesn´t like empty fields or content.

Please help. Thanks

Problem With Empty Field ..
Im trying to update a table in my database.. and im using ".Find" to locate the column to be updated..

but it must only update if txt_name = fld_name and lbl_date = fld_date and fld_time_out is empty ..

my problem is , the fld_time_out = ' " + empty + "'" doesnt work..

any ideas or alternative i can use ? .. thanks..

please check my code:

Adodc1.Recordset.Find "fld_name = '" + txt_name.Text + "'"
Adodc1.Recordset.Find " fld_date = '" + lbl_date.Caption + "'"
Adodc1.Recordset.Find " fld_time_out = '" + empty + "' "

If Adodc1.Recordset.EOF Then
MsgBox ("..")
Else

With Adodc1.Recordset
.Fields!Fld_time_out = lbl_time
.Update

End With

MsgBox ("record saved")

Adodc1.Refresh

End If

Updating An Empty Field
Im trying to update all my fields in Access where the fld claimnumber is blank

Set mrstDWCActivityLog = GdbDWC.OpenRecordset("tblactivitylog")
GdbDWC.Execute "UPDATE tblactivitylog " & _
"SET fldClaimNumber='" & dbcClaimNumber.BoundText & "' " & _
"WHERE fldClaimNumber='" & "" & "'"

Im Just not sure how to find an empty field.
can anyone help

Refering To An Empty Field?
I can't figure this one out, how would you refer to an empty field in the database? Closest I can come with is something like:

Code:
If datLeague.Recordset!Name="" then
Something along those lines... except databases obviously can't be refered to as "".

What's the code you need to make this reference?

Select Where Field Is Empty ?
Hi everyone ...
Using VB6, Access2000 and ADO, I need to do what should be a simple select statement.
In plain english I need to Select everyting from table1 where field3 is NOT empty.
The field data type is Text.
When I use the SQL below, I get a syntax error.
Here is the SQL ... any help?

Code:
strSQL = "Select * From Table1 Where Field3 <> """

SQL For Access - Where Field Is/is Not EMPTY
How can I query where a field is either empty or not empty?

I would normally do...

VB Code:
recordset.Source = "SELECT * FROM table WHERE [FIELD] = 'xyz';"

but obviously in this case i want to do it so it'll bring back the records if the field is zero length and another for if it is not zero length. Anyone know how to do this?

thanks.

How To Test A Field Is Empty?
Hi there,

Could someone tell me how to test a field is empty or not? Thanks

K

How Do I Evaluate An EMPTY RS Field?
I have a SELECT that brings back records but sometimes one of the Fields in the RS will be empty. So, whenever I use the code below on an empty RS field value I get the error, "can not be found in the ordinal", message. How can I just check if the RS field value has something in it?

Code:
if len(rs("ResponseTest")) = 0 then

thanks

Populate Empty Field
Using code- Is there anyway to enter a value into an empty field in a recordset? Maybe by using an If...Then statement???

Thanks for help
paul

Characterization Field Empty
This probably isn't the correct forum, but I'm a VB guy so....

I'm using the Indexing Service for searching through the contents of PDF documents.  A couple of the fields I want to get data from, Characterization and DocSubject, are returned as Null.  I set the cached = true for these properties, made sure the Generate Abstracts was set to True for the Indexing Service (catalog inherits this value), restarted the service, and initiated a full rescan of all directories of the catalog.

According to articles I've read I understand it may take some time before the word lists are updated, but it's been several hours and I only have a small amount of test documents in the directories (about 30 files, most are about 50KB, the biggest is about 500KB).

I did find one web article that mentioned a GenerateCharacterization attribute in the Registry that should be set to 1.  Of the three attributes I found in the Registry with that name, one was set to zero.  So I set that to 1, but it didn't make a differnce.

Here's a sample of my code:
CODE    Dim rs As ADODB.Recordset
    Dim strConn As String
    Dim strSearch As String

    On Error GoTo ErrHandler

    Set rs = New ADODB.Recordset
    strConn = "Provider=MSIDXS;Data Source=MyCatalogName"

    strSearch = "SELECT Size, DocTitle, DocSubject, FileName, Characterization from SCOPE()"

    rs.Open strSearch, strConn

    'rs("Characterization") and rs("DocSubject")
    'are both Null!

Data Field (Empty) Not Found
I am trying to create a limited dynamic data report - I scan the field names in from the recordset and then fill the text controls - data field properties with the names scanned in etc.

Unfortunatelly some times there are not enough field names to fill all the text controls and so I get the data field (empty) not found message.

Does anyone know how to assign a null value to a data field, or some way round it ??

On error resume next does not work

Cheers
Paul

Saving A Empty Textbox To A Db Field
im getting the zero-length string error, this isn't a problem when im saving a new record, i just dont update those fields that are blank,, but when im trying to edit a field i need to remove the old data and replace it with the new data... even if the new data is "".... how can i take an empty string and save it to a field?

Empty Field Causing A Problem
Just as one problem gets sorted up crops another

I've got a small database with 4 records in it (see attachment) but on the fourth record the person didn't have a company name so I left the field blank

I expect to have many records with blank fields in them but my program doesn't like them being blank and comes up with some 'invalid use of null' error

Instead of running through and putting the data into the textboxes for each record and just that record I think its trying to put into each textbox the data that comes next in the database - if that makes sense

Any Advise?

How To Check An Empty Field On A Form?
Here's the code I have for a text field on my form.

If Len(.Text1(5).Text) > 0 Then
If Not IsDate(.Text1(5).Text) Then
MsgBox "The received date must be a valid date."
Exit Sub
End If
End If

I always gives me the msgbox error even when I don't put anything in that field. I can't figure out why. The field's default text is empty, so I don't understand how it's getting inside that first if statement. Anyone have an idea or a better way to do this? thanks.

Data Report - Empty Field
I created a data report with ADO and the record set is prepared on the fly. Some of the columns in the record set will be empty. But the data report does not allow me to have empty fields. How do I have it ?

Thanks in advance

James

Recordset Is Empty
i used to check for empty recsets by doing:

Code:
if rs.bof and rs.eof then 'empty
else 'not
end if

when i do this:, i have to use the following---

Code:
sql = "SELECT max(txnnum) as Maxi FROM transactions"
if isnull(rs.fields("maxi")) or rs.fields("maxi").value="" then 'empty
else 'not
end if

can someone esssplain the difference? and why the one wont work?

Recordset Is Empty
I have a problem with recordsets that just doesn't make any sense. I have a query in Access 2002 that I want to export to Excel 2002. I have many memo fields so I cannot use the export tool within either application becuase they truncate those fields. The code below always returns an empty recordset and I get the method 'CopyFromRecordset' of object 'Range' failed error. I would appreciate any help on this subject.

Code:
Dim conn As New ADODB.Connection
Dim recset As New ADODB.Recordset

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim strDB as String

strDB = "c:database.mdb"

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strDB & ";"

recset.Open "Select * From Test", conn

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

xlWs.Cells(2, 1).CopyFromRecordset recset

recset.Close
conn.Close
Set recset = Nothing
Set conn = Nothing

Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

Empty Recordset?
Hi Again...
Another stupid question...what's wrong with my code?
I'm using a wildcard in my sql statement because there are numerous records with different sub-section endings and I want to access all sub-section records. The recordset returned is empty (well, at EOF)...
Does this make sense?
TIA, Joni

Set oRecSet = New ADODB.Recordset
sSQL = "SELECT * FROM Seaview WHERE Grave_Number = '" & _ Grave_Num & "*'"
oRecSet.Open sSQL, oConn, adOpenDynamic

Dim i As Integer
i = 0
Do While Not oRecSet.EOF
If i = 0 Then
frmPlot.tbSubSect1.Text = oRecSet.Fields("Grave_Sub_Section").value
frmPlot.tbStatus1.Text = oRecSet.Fields("Status").value
If oRecSet.Fields("Plot_Type").value & "" <> "" Then
frmPlot.tbPlotType1.Text = oRecSet.Fields("Plot_Type").value
End If
ElseIf i = 1 Then
frmPlot.tbFirst2.Text = oRecSet.Fields.Item("First_Name").value
frmPlot.tbLast2.Text = oRecSet.Fields.Item("Last_Name").value

End If
'=====================
' increment counter
'=====================
i = i + 1
oRecSet.MoveNext
Loop
End If

Empty RecordSet ??
HI !!!

I'm gonna try to be as brief and clear as possible:

I'm opening a table "workers" from a database with the following simple SQL command: "SELECT middle_name FROM workers WHERE ID_State = 4". The thing is that I want to call a certain function if the "middle_name" is different from an empty string (""). Otherwise, if "middle_name" is not different from an empty string, I call another function.

My problem is this: "middle_name", most of the times, comes with blank spaces (" " or, when the user just pressed enter, adding a new line, or both). Any of these, of course are not valid "middle names". So I'd like to know if there's a way to fix this ???

Empty Recordset
I added a record to the table and I am still getting empty recordset. I have two tables:

tblClassification - id, classification
tblSnipplets - id, dateadded, classification, code, title

I am getting the following error:
Run-Time Error '3021'. Either BOF or EOF is True, or the current record has been deleted.

Code:
Private Sub initializeTree()

tvwItems.ImageList = imgList

Dim nodClass As Node
Dim nodTitle As Node

tvwItems.LineStyle = tvwRootLines
tvwItems.Nodes.Clear

'Opens the recordset for Class
Set rsClass = New ADODB.Recordset
strSQLClass = "SELECT id, classification FROM tblClassification ORDER BY classification"
rsClass.Open strSQLClass, conTree, adOpenStatic, adLockOptimistic

'set node for treeview
If rsClass.RecordCount > 0 Then
While Not rsClass.EOF
Set nodClass = tvwItems.Nodes.Add(, , (rsClass.Fields("classification")), (rsClass.Fields("classification")), 1)
rsClass.MoveNext
DoEvents
Wend
End If

'Opens the recordset for Title
Set rsTitle = New ADODB.Recordset
strSQLTitle = "SELECT title, classification, code, dateadded FROM tblSnipplets WHERE classification LIKE '" & (rsClass.Fields("classification")) & "%'"
rsTitle.Open strSQLTitle, conTree, adOpenStatic, adLockOptimistic

'set node for treeview
If rsTitle.RecordCount > 0 Then
While Not rsTitle.EOF
Set nodTitle = tvwItems.Nodes.Add((rsClass.Fields("classification")), tvwChild, (rsTitle.Fields("title")), (rsTitle.Fields("title")), 2)
rsTitle.MoveNext
DoEvents
Wend
End If

End Sub

How To Tell If Recordset Is Empty
How can I tell if a recordset is empty?

rst.EOF seems to work, but can a filled recordset open and be positioned at eof under any circumstance (ie only one record)?

i cannot use rst.movefirst because it will cause an error if it truly is empty.

there has to be a standard way of doing this.

Thanks for any help

Empty Recordset
Hi,

I want to create an empty recordset so that I can add values to it and decide when I want to update the database. How do I do that?

Thanks

H.

Empty Recordset?
How do I test the results of an SQL Query to determine if anything was returned?

This does not work obviously, but its all I've got

Code:
If authRS <> Null Then
End If

Empty Recordset
Hi,
I'm running a very simple SQL statement in order to obtain a recordset.
When I run it as a MySQL query it works like a charm, but when I execute it as a ADODB.Command.CommandText the recordset comes back empty!

The SQL is "SELECT * FROM tblDeliveries WHERE OriginID = 1 ORDER BY delDate", MySQL version:4.0.13 and I use VB 6.

Thanx!

Empty Recordset
To anyone in group,

i've created a table which has no records,
now after designing the form and opening of a recordset i get the
error whether eof or bof is true ,i want to shift the focus to add
command button if recordset set is empty but while moving the
setfocus on command table in form load event i get an error.

please suggest me how to allow the user to enter the record
while recordset is empty thru form.

thanks in advance

Regards,
Prashant.

Empty Recordset
Hi all,

I'm trying to populate a db grid with some data.
If there is some data on a variable it should give me the records that
contains part of the specified data, but the only way that I can get a
recordset is when there's no condition.
The SQL is file, because I've tested it in Oracle.

This is the code that is giving me problems:
If vNome <> "" Then
' This always turn out empty
dcTabela.RecordSource = "SELECT conta, posto, nome, localidad,
cpostal, telefone, telemovel, fax, contacto, d_ult_alt, mail FROM
utentes " & _
"WHERE nome LIKE '" & Trim(vNome) & "%'
ORDER BY nome ASC"
Else
' This give me all the data just fine
dcTabela.RecordSource = "SELECT conta, posto, nome, localidad,
cpostal, telefone, telemovel, fax, contacto, d_ult_alt, mail FROM
utentes ORDER BY conta asc"
End If
dcTabela.Refresh

I'm using Oracle 8i and VB6.
The data control have the data type Jet, and the recordset type is
Snapshot.

Can anyone help me?
Thank you.

How Do You Know If A Recordset Is Empty??
A newbie question... how can you tell if a recordset of a query is emtpy?
Code:
sSQL = "SELECT adas_id, var_code " & _
        "FROM table " & _
        "WHERE rtu_nb = " & rs.Fields("rtu_cs_nb").Value & _
        " AND mp_nb = " & rs.Fields("mp_nb").Value
Set rs1 = Mod_Globals.conTrans.Execute(sSQL, Anzahl, adCmdText)

If rs1.?????? is empty then....

Edited by - ezrasuncle on 4/30/2004 6:30:16 AM

Recordset Sometimes Empty, Sometimes Not
Hi!

I have a weird problem with Recordsets. I've written this code (further down) yesterday and it all worked fine. At 8am today i started the code again and get an "Recordset BOF or EOF......" error, when trying to access it. Nothing has changed since yesterday evening...!? The connection to the database works fine and it makes no difference what SQL Statement i'm using..i just don't get any results since today's morning. I've also tried a different database...but it's exactly the same.

Here's the code: (it should read some customers from a database an write it to our customers SAP BO Database)

 Dim oCompany As SAPbobsCOM.Company
 Dim oBp As SAPbobsCOM.BusinessPartners
 Dim dbCon As New ADODB.Connection
 Dim rs As New ADODB.Recordset
 Dim con As String
 Dim i As Integer
 Dim nerr As Long
 Dim err As String
 Dim Source As String
 Dim bool As Boolean = False

 Source = "Select Z_FS, Debitor, Bezeichnung, Name1, COALESCE(Strasse,0) as Strasse,COALESCE(PLZ,0) as PLZ FROM FIBU_STAMMDATEN where Zusatz = Null"
 con = "DSN=Vivendi;uid=sa;"

 Try
 dbCon.Open(con)


 rs.Open(Source, dbCon)

 oCompany = New SAPbobsCOM.Company
 oCompany.Server = "*****"
 oCompany.CompanyDB = "******"
 oCompany.language = SAPbobsCOM.BoSuppLangs.ln_German
 oCompany.UserName = "*******"
 oCompany.Password = "*******"
 oCompany.UseTrusted = False

 If oCompany.Connect() = 0 Then
 MsgBox("Connected to " + oCompany.CompanyDB + " at " + oCompany.Server + ".")
 oBp = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oBusinessPartners)

 If rs.BOF = False Then

 Do
 oBp.CardName = rs.Fields.Item("Name1").Value
 oBp.CardCode = rs.Fields.Item("Debitor").Value
 oBp.CardType = SAPbobsCOM.BoCardTypes.cCustomer
 oBp.Address = rs.Fields.Item("Strasse").Value
 oBp.ZipCode = rs.Fields.Item("PLZ").Value

 If (0 <> oBp.Add()) Then
 MsgBox("Failed to add item")
 bool = True
 End If
 rs.MoveNext()

 Loop Until rs.EOF = True Or bool = True

 Else : MsgBox("Recordset not ready/no data available")
 End If
 End If
 dbCon.Close()
 oCompany.Disconnect()

 Catch ex As Exception
 MsgBox(Convert.ToString(ex))
 End Try
 End Sub

Thx for the help in advance!

Empty Recordset
Hello,
I have connected to a database in the following manner:

<%
If IsObject(objConn) then
set objConn = Session("objConn")
else
set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=oasis; Database=Oasis_ATL;"
strConnection = strConnection & "UID=; PWD=cms;"
objConn.Open strConnection
End If

I am executing a query in the following manner:

'Execute the query and store it in a recordset object
set objRS = objConn.Execute(strQuery)

I realize that since I have done it this way, objRS.RecordCount will always return -1. Despite this fact, I would like to send an error message to the user if the recordset is empty
Example:

<%
'Return an error page if item was not found in the database
If objRS.RecordCount ?????? then
%>
<HTML><BODY BACKGROUND="paper.jpg"> <HR> <CENTER>error! THE REQUESTED FSA "<%=strFsa %>" WAS NOT FOUND IN THE DATABASE PLEASE RESELECT A SEARCH CRITERIA AND TRY AGAIN... <HR></CENTER></BODY></HTML>

If the RecordCount always returns -1, how can I check if the recordset is empty?

Empty Recordset
Is there a way I may connect to a database table with a recordset, but not import any of the records already in the table? I mean without specifying some query condition that would return zero records. Basically I want to do inserts to an initially disconnected recordset and do a batch update at the end.

Thanks! I appreciate all the help I have received in the past.

I Need A Way To Update A Field Based On The Value Of Another Field In The Recordset
Basically, I have this form that allows you to take temporary records and place them in the permanent table, and assigns them a CertNo based on the last available CertNo. This is all works fine, but now there will be a Different CertNo depending on which certification you are. I need to be able to look up the last CertNo for each Cert type.

Below is the code that assigns the data to the query that the will hold the data.

Any ideas?

Code:
Private Sub cmdMigrate_Click()
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strCertNo As String
Dim strSQL As String
Dim strQueryName As String
Dim dbs As Database
Dim rst As Recordset

Set frm = Forms!frmMigration
Set ctl = frm!lstMultiSelect
strSQL = "SELECT * FROM [qryExamPassed] WHERE [qryExamPassed].[SSAN]='"
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & "' Or [qryExamPassed].[SSAN]='"
Next varItem
strSQL = Left$(strSQL, Len(strSQL) - 28)
strQueryName = "qryExamMigrate"
CreateCertQuery strSQL, strQueryName
Set dbs = CurrentDb
Set rst = OpenForSeek("tblCertification")
rst.Index = "CertNum"
rst.MoveLast
strCertNo = rst!CERT_NO
rst.Close

Set rst = dbs.OpenRecordset("qryExamMigrate")
Do
strCertNo = LTrim(Str(Val(strCertNo) + 1))
rst.Edit
rst("TEMP_CERTNO") = strCertNo
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close

DoCmd.Close
DoCmd.OpenForm "frmMigration2"
End Sub

Return An Empty String From A Ado Field Containing A Null Value
Is there a way to return an empty string from a null ado field in Visual Basic 6? I use nz() in Access, looking for a similiar method.

Validate The Empty Field Based On Tab Index
Code:

How can i validate the empty field based on tab index ?? The following code only can found out the empty field randomly.

Public Function ValidateEmptyField(formName As Form) As Boolean
ValidateEmptyField = False
Dim ctrl As Control
For Each ctrl In formName.Controls
If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Or TypeOf ctrl Is RichTextBox Or TypeOf ctrl Is ImageCombo Then
If Not ctrl.Visible = False Then
If ctrl.Text = vbNullString Then
MsgBox "Please Fill up the " & ctrl.Tag, vbCritical, "Save Failed"
If ctrl.Enabled = True Then
ctrl.SetFocus
End If
ValidateEmptyField = True
Exit Function
End If
End If
End If
Next

End Function

Thanks for any reply !!!

Empty Date Field Entry Problem
I have several text boxes in a VB6 entry form that take short dates and saves them to an Access 2002 db. If they all get filled the save works fine. If any one is left empty I get the following error message: Syntax error in date in query expression '##'.

The code used to Enter and Save them is:

VB Code:
Con.Execute "INSERT INTO Contract (Date_Filed [and 17 other fields]) VALUES (#" & TxtDate_Filed.Text & "#, [and 17 other field values])"

I have read about a "Null" problem but don't know if this is an instance of that since I don't get the "Invalid Use of NULL (94)" error message. Any help appreciated. --Ed

How To Insert Empty String In Numeric Field?
Hi,

I'm connecting to a database via ADO. The table contains several fields that are of the Number data type.

If I go in the table itself, I can clear out the values in these fields but how can this be done in VB?

I try and use the following peices of code witout any luck:

oRs!Field1 = ""

oRs!Field1 = vbNull

oRs!Field1 = vbNullString

It keeps returning the type mismatch error. Please keep in mind that I do not want to put 0 into the field, I want to make them blank, or null. This works if the field is a Text type, but not with Numeric type.

Any help or examples would be greatly appreciated..

Dan

Showing Datareport Empty Field Borders
I have a datareport that works perfectly in every way, except that my empty fields do not show borders. I have some fields that get their data from a database, which work fine. The problem is the empty fields, which need to show just empty cells, but instead have no border and are just empty space. (The user will print out the form and fill in these last parts by hand, so I need to make the form idiot resistant and clearly show the spaces to write in. The borders for all the fields are set as "1-rtBSSolid". The empty fields are labels. Any help is appreciated

(btw, I posted this to another forum and have gotten no help, so I'm hoping DevShed will come through for me )