• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This function reads the idfield value out of any combobox. One code fits all.

This function reads the idfield value out of any combobox. One code fits all.

This function reads the idfield value out of any combobox. One code fits all.

Rate This function reads the idfield value out of any combobox. One code fits all.

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

' call the function LookupValue
TempID = LookupValue("SELECT Artist FROM Bands", "ArtistID", "cmbArtist", "Artist")


' the function LookupValue
Public Function LookupValue(SQLStatement As String, IDFieldname As String, Value As String, Fieldname As String)

Dim ado As ADODB.Connection
Dim rsTemp As ADODB.Recordset

Set ado = CreateObject("ADODB.Connection")
ado.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\CDCollectie.mdb;Persist Security Info=False"
    
Set rsTemp = CreateObject("adodb.recordset")
    With rsTemp
        .CursorType = adOpenDynamic
        .LockType = adLockBatchOptimistic
        .Open SQLStatement, ado
    End With
    ' if not excist, make a new record
    If rsTemp.EOF = True Then
        rsTemp.AddNew
            rsTemp(Fieldname) = Value
            rsTemp.UpdateBatch
            LookupValue = rsTemp(IDFieldname)
    Else
        Do While Not rsTemp.EOF
            LookupValue = rsTemp(IDFieldname)
            rsTemp.MoveNext
        Loop
    End If
    
rsTemp.Close
Set rsTemp = Nothing
  
ado.Close
Set ado = Nothing
        
End Function