How do you use a select case statem

[bdavey]

How do you use a select case statement when using a recordset or is this possible? We are getting a data type mismatch error and are unsure why?


This is the code we have thus far:
Private Sub Product_Update()

Dim dbs As DAO.Database
Dim rstCD As DAO.Recordset
Dim strsql As String

'Select all records from Sales Open 1 Engineering Order

Set dbs = CurrentDb()
strsql = "SELECT * FROM [SLS_OPN_1_ENG]"
Set rstCD = dbs.OpenRecordset(strsql)

Select Case rstCD
Case rstCD![Application] = "DAL"
rstCD![Product] = rstCD![New Digt Acc Cd]
Case Else
rstCD![Product] = "OTHER"
End Select

End Sub

 

[jebry]

Hi!

Try this:

Private Sub Product_Update()

Dim dbs As DAO.Database
Dim rstCD As DAO.Recordset
Dim strsql As String

'Select all records from Sales Open 1 Engineering Order

Set dbs = CurrentDb()
strsql = "SELECT * FROM [SLS_OPN_1_ENG]"
Set rstCD = dbs.OpenRecordset(strsql. dbOpenDynaset)

With rstCD
If .EOF = True And .BOF = True Then
Call MsgBox("There were not records.&quot
Else
.MoveFirst
Do Until .EOF
Select Case !Application
Case "DAL"
![Product] = ![New Digt Acc Cd]
Case Else
![Product] = "OTHER"
End Select
Loop
End If
End With

End Sub

hth
Jeff Bridgham
bridgham@purdue.edu

 

[bdavey]

We are on the right track, however,we are no longer getting an error message (it is compiling properly). However, it is not updating the fields in the table? What step are we missing?

Thanks!

 

[jebry]

Hi Again!

Sorry, my mistake!

With rstCD
If .EOF = True And .BOF = True Then
Call MsgBox("There were not records.&quot
Else
.MoveFirst
Do Until .EOF
Select Case !Application
Case "DAL"
.Edit
![Product] = ![New Digt Acc Cd]
.Update
Case Else
.Edit
![Product] = "OTHER"
.Update
End Select
Loop
End If
End With

hth
Jeff Bridgham
bridgham@purdue.edu

 

[bdavey]

Thank you so much!!!! It worked! You have been a big help!