lookup value in tabel and store in continuous form
hi
mmcdonal, this one is for you. you gave me the following code for the lookup in my database to lookup the value in a table.
Private Sub Naam_afdeling_AfterUpdate()
'Takes the value from the combo box
Dim sAfdeling As String
sAfdeling = Me.Naam_afdeling
'Starts a connection to the tblAfdelingenOpvTT to get the telefoonnr
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
'Selects the record selected by the combo box from the table
sSQL = "SELECT * FROM tblAfdelingenOpvTT WHERE [Naam afdeling] = " & _
"'" & sAfdeling & "'"
Set cn = New ADODB.Connection
With cn
.ConnectionString = CurrentProject.Connection
.CursorLocation = adUseClient
.Open
End With
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open sSQL, cn
'Displays the selected telefoonnr in the text box on the form.
Me.Telefoonnummer = rs.Fields(1)
End Sub
I told you this works fine, and it still does. Now I want something like this in another form. This form has is a continious form (don't know if this is important). What I want is when the form opens, that the database looks up the phone number and store it the same way, but only if their is no value in the phone number field already... It should look it all up when it opens, so it is not dependant of the primary key... when it opens it should look it up for all the records on the form. I changed the code to the following for this form:
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
If IsNull(Me.Telefoonnummer) Then
'Takes the value from the combo box
Dim sAfdeling As String
sAfdeling = Me.Naam_afdeling
'Starts a connection to the tblAfdelingenOpvTT to get the telefoonnr
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String
'Selects the record selected by the combo box from the table
sSQL = "SELECT * FROM tblAfdelingenOpvTT WHERE [Naam afdeling] = " & _
"'" & sAfdeling & "'"
Set cn = New ADODB.Connection
With cn
.ConnectionString = CurrentProject.Connection
.CursorLocation = adUseClient
.Open
End With
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.Open sSQL, cn
'Displays the selected telefoonnr in the text box on the form.
Me.Telefoonnummer = rs.Fields(1)
End If
End Sub
When I try to run this by opening the form I get an error. If I go to the VBA to debug the line Me.Telefoonnummer = rs.Fields(1) creates the error. What should I change to make this work as well? Do you have any idea?
thanks
|
