VB with Acess DB

[glipuma]

Hi - I have a couple of questions.. any response is appreciated!

First, I am accessing an Access 97 database with VB 6.0. I should first tell you that I;m currently running Access 2000 but since there was too big an issue making it work with VB 6.0 in Access 2000, I'm pressed for time so I'm working with it in its 97 version.

The first thing I have a problem with is that I have an add hoc select statement form and when there are no results, it gives me a VB error, "No Records Found", then you have to end the whole program. How can I bypass this and just have a message box indicating this to the user?

The second problem I'm having is that I cannot get an add hoc Insert statement to work, here's some of my code for that:

ElseIf (cmbBeg.Text = "INSERT&quot Then
SQL = "insert into " & cmbInsertI.Text
SQL = SQL & " (EmpID, EmpName, BirthDate, JobID, PayRate)"
SQL = SQL & " values (" & txtInsertEmpID.Text & ", " & DBTextFmt(txtInsertEmpName.Text)
SQL = SQL & ", " & CStr(DBDateFmt(txtInsertBirth.Text)) & ", " & txtInsertJobID.Text
SQL = SQL & ", " & txtInsertPay.Text & &quot"


Set fEmpRS = fEmpDB.OpenRecordset(SQL)


Call OpenResults
End If


The error I get is: Run-time error '3219' Invalid operation.

The SQL ends up being:
insert into Employee (EmpID, EmpName, BirthDate, JobID, PayRate) values (100, 'John Doe', #04/04/1977#, 2, 19.00)

what's wrong with this where I keep getting this error?

again, ANY help is appreciated, I'm pressed for time!

thanks!!

 

[DougP]

First of all you should just put one question per post
So everyone knows which thing got answered and did not get answered etc.

Question1 you need and error trap.

In function or sub where the error occurs add this just below the sub/function name

Sub xxxxxx
On Error Goto Errhandler '<<<<< Add this line

Now right above the &quot;End Function&quot; or &quot;End Sub&quot; put this

Exit_me:
Exit Function ' Or if this is a Sub &quot;Exit Sub&quot;

Errhandler:
Select Case Err.Number
Case 3021
' No current record

Case Else
MsgBox &quot;Error # &quot; & Err.Number & &quot; &quot; &Err.Description, vbInformation, &quot;In sub xxxxxxx&quot;
Resume Exit_me
End Select

End sub

Now when the messages pops up look at the number 3021 or 54 or waht ever
This is what goes in the Case statements
See I have one 3021 which is No record so if there is no record it comes down to the trap finds the 3021 and then returns to keep going.

OK


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[TimLarkin]

As to error 2:
You don't say what your using, ie: DAO, RDO, ADO.
To my knowledge, you can't <b><i>open</i></b> a recordset with an Insert SQL statement.
As a quick example using ADO:
'not all code shown
Dim comADO As ADODB.Command
.
.
.
ElseIf (cmbBeg.Text = &quot;INSERT&quot Then
SQL = &quot;insert into &quot; & cmbInsertI.Text
SQL = SQL & &quot; (EmpID, EmpName, BirthDate, JobID, PayRate)&quot;
SQL = SQL & &quot; values (&quot; & txtInsertEmpID.Text & &quot;, &quot; & DBTextFmt(txtInsertEmpName.Text)
SQL = SQL & &quot;, &quot; & CStr(DBDateFmt(txtInsertBirth.Text)) & &quot;, &quot; & txtInsertJobID.Text
SQL = SQL & &quot;, &quot; & txtInsertPay.Text & &quot&quot;
comADO.Execute SQL