ADO Method help in Access ADP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ADO Method help in Access ADP

Author

Topic

charles101
Starting Member

16 Posts

Posted - 2007-02-21 : 20:02:36

Hi all,

Ive currently upgraded my Access application using the upsizing wizard to an Access.adp project.

Im having trouble with some off the ADO calls in my existing Access forms which i modified to the following

In the forms it calls the following code to populate the fields on the form when the combobox sample number is selected

Private Sub UpdateAfterSample()

Dim rs As ADODB.Recordset 'modified too ADODB
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strSQL As String
Dim strRowSource As String
Dim lRecords As Long
Dim sampletype

Set cn = CurrentProject.Connection

cmbSampleNumber.SetFocus
strSQL = "Select * " & _
"FROM [sq Inbound Samples] WHERE [sq Inbound Samples].[Sample Number] " = & gstrSample

'Changed too used ADO connection
'Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
'Set rs = cn.Execute(strSQL, adCmdText)
Set rs = cn.Execute(strSQL, dbOpenSnapshot)
If (cmbSampleNumber <> "") And Not (rs.BOF And rs.EOF) Then
Edit_Mode.Caption = "Edit Mode"
With rs
'cmbFinishDate = Format(gfnCstrField(rs, "Finish Date"), "DD/MM/YYYY")
'cmbStockPileNumber = gfnCstrField(rs, "Stockpile Number")
cmbSampleNumber = gfnCstrField(rs, "Inbound Samples.Sample Number")
cmbSampleType = gfnCstrField(rs, "Sample Type")
txtStartDate = Format(gfnCstrField(rs, "Start Date"), "DD/MM/YYYY")
txtStartTime = Format(gfnCstrField(rs, "Start Date"), "HH:MM")
txtFinishTime = Format(gfnCstrField(rs, "Finish Date"), "HH:MM")
txtSampleTonnes = gfnCstrField(rs, "Sample Tonnes")
txtMissedSamples = gfnCstrField(rs, "Missed Samples")
txtDowntime = gfnCstrField(rs, "Sampler Downtime")

End Sub

Here is the gfnCstrField function

Public Function gfnCstrField(rs As ADODB.Recordset, sgField As String) As String

Dim sgResult As String
On Error Resume Next

gfnCstrField = ""
If IsNull(rs(sgField)) Then
sgResult = " "
Else
sgResult = CStr(rs(sgField))
End If
gfnCstrField = sgResult

Exit Function
fnErr:
' MsgBox "Err# " & CStr(Err.Number) & " " & CStr(Err.Description), vbCritical, _
"Procedure: gfnCstrField"
End Function

I am getting an error at everyline where it the gfnCstrField function is being called too populate the forms. The error is saying

Runtime Error '-2147352567 (80020009)
The Value you entered isnt valid for this field'

I would say the ADODB connections are not defined correctly or im using the incorrect properties..Can someone help me with the correct way off using ADODB and what is happening here. Also where is the best place too define the CN connection method as I define this in every function and then close it afterwards.

Thanks

charles101
Starting Member

16 Posts

Posted - 2007-02-22 : 17:24:47

Guys Ive solved it !!! however can anyone tell me the best ADO methods too use

Im currently using

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set rs = cn.Execute(strSQL, dbOpenSnapshot)
Set cn = CurrentProject.Connection

Where is the best place too put these declarations so it is efficiently maintained and executed...im thinking sub_formload , if i put it here where do i close the connection?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources