Using DAO to write to textbox in userform
 

Hi

I've used the code below to successfully update a textbox on a
worksheet.

When I tried to call this from a command button on a userform, it
failed with the error compile error: user defined type not defined

on the Dim wspDefault As Workspace line.

I'm grateful for any suggestions.

Thanks,

Dan

Sub CreateRecordSet()

Dim oldDbName As String
***Dim wspDefault As Workspace
Dim dbsEAIQuote As Database
Dim strSQL As String
Dim strCompetitorPart As String
Dim strEAIPart As String
Dim rstFromQuery As Recordset

strCompetitorPart = Sheet1.TextBox1.Text
strEAIPart = Sheet1.TextBox2.Text


'Set the path to the database
oldDbName = "C:/My Documents/Quote/EAIQuote_be.mdb"

'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)

'Create a Database object
Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName)

'The SQL statement
strSQL = "SELECT tblCompetitorScrubbed.CompetitorNumber, " & _
"tblCompetitorScrubbed.EAIPartNumber FROM
tblCompetitorScrubbed " & _
"WHERE (tblCompetitorScrubbed.CompetitorNumber= '" &
strCompetitorPart & "')"

'Create a Snapshot Type Recordset from the SQL query
Set _
rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)

'Show the number of fields returned
MsgBox "there are " & rstFromQuery.Fields.Count & _
" fields that were returned"

'Move to the last record in the recordset
rstFromQuery.MoveLast

'Put the EAI part number in textbox2
Sheet1.TextBox2.Value = rstFromQuery!EAIPartNumber
'Show the number of records returned
MsgBox "there are " & rstFromQuery.RecordCount & _
" records that were returned"

End Sub

Using DAO to write to textbox in userform
 

I forgot to mention I'm using Excel 2000.

Using DAO to write to textbox in userform
 

Ok. I neglected to add the Microsoft DAO 3.6 Object

Using DAO to write to textbox in userform
 

In VBA menu Tools - Referrences check the following box

Microsoft DAO 3.6 Object Library

"dan dungan" wrote:

Hi

I've used the code below to successfully update a textbox on a
worksheet.

When I tried to call this from a command button on a userform, it
failed with the error compile error: user defined type not defined

on the Dim wspDefault As Workspace line.

I'm grateful for any suggestions.

Thanks,

Dan

Sub CreateRecordSet()

Dim oldDbName As String
***Dim wspDefault As Workspace
Dim dbsEAIQuote As Database
Dim strSQL As String
Dim strCompetitorPart As String
Dim strEAIPart As String
Dim rstFromQuery As Recordset

strCompetitorPart = Sheet1.TextBox1.Text
strEAIPart = Sheet1.TextBox2.Text


'Set the path to the database
oldDbName = "C:/My Documents/Quote/EAIQuote_be.mdb"

'Create a default workspace Object
Set wspDefault = DBEngine.Workspaces(0)

'Create a Database object
Set dbsEAIQuote = wspDefault.OpenDatabase(oldDbName)

'The SQL statement
strSQL = "SELECT tblCompetitorScrubbed.CompetitorNumber, " & _
"tblCompetitorScrubbed.EAIPartNumber FROM
tblCompetitorScrubbed " & _
"WHERE (tblCompetitorScrubbed.CompetitorNumber= '" &
strCompetitorPart & "')"

'Create a Snapshot Type Recordset from the SQL query
Set _
rstFromQuery = dbsEAIQuote.OpenRecordset(strSQL, dbOpenSnapshot)

'Show the number of fields returned
MsgBox "there are " & rstFromQuery.Fields.Count & _
" fields that were returned"

'Move to the last record in the recordset
rstFromQuery.MoveLast

'Put the EAI part number in textbox2
Sheet1.TextBox2.Value = rstFromQuery!EAIPartNumber
'Show the number of records returned
MsgBox "there are " & rstFromQuery.RecordCount & _
" records that were returned"

End Sub

Using DAO to write to textbox in userform
 

Thanks, Joel.