Execute SQL to get a value

  • Thread starter Thread starter FTL
  • Start date Start date
F

FTL

Hi
I am working in Access VBA and have made a SQL sentence to get a the name of
a salesman given his number.
But... I cant figure out how to run this SQL.... any suggestions?


Dim vSelgerNr As Integer, vSelgerNavn, vSqlSelger As String
vSelgerNr = Selgernummer.Value <- Gets the
salesmans number from the form vSqlSelger = "SELECT
tbl_90_Selgere.Selgernavn
FROM tbl_90_Selgere
WHERE tbl_90_Selgere.Selgernr = " & vVårRefNr
vSelgerNavn= ???????????
 
FTL said:
Hi
I am working in Access VBA and have made a SQL sentence to get a the name of
a salesman given his number.
But... I cant figure out how to run this SQL.... any suggestions?


Dim vSelgerNr As Integer, vSelgerNavn, vSqlSelger As String
vSelgerNr = Selgernummer.Value <- Gets the
salesmans number from the form vSqlSelger = "SELECT
tbl_90_Selgere.Selgernavn
FROM tbl_90_Selgere
WHERE tbl_90_Selgere.Selgernr = " & vVårRefNr
vSelgerNavn= ???????????
Click to expand...

Hi, you can use dlookup:

vSelgerHavn = dlookup ("Selgernavn", "tbl_90_Selgere", "Selgernr = " &
vSelgerNr)

Alex
 
Another way would be to use a recordset (you need to have the DAO Type Library in Visual Basic Editor > Tools > References). Use an event such as the Form's Current Event or even a Command Button to trigger the code. You could have the code fill in a Text box on the form, pop us a message or insert it somewhere. Don't forget to substitute your own names:-

Dim db as database 'it is good to fully declare all variables
Dim rs as recordset
Dim vSqlSelger As String

Dim vSelgerNr As Integer
Dim vSelgerNavn As String
Dim vSelgerNr = Selgernummer.Value

vSqlSelger = "SELECT tbl_90_Selgere.Selgernavn " & _
"FROM tbl_90_Selgere " & _
"WHERE tbl_90_Selgere.Selgernr = " & Me.txtTextBox_SelgerNavn
'name of text box on form

If Me.NewRecord = False Then 'if this is not a new record

set db = CurrentDb
set rs = db.OpenRecordset(vSqlSelger)

If rs.RecordCount > 0 Then 'if a record exists - prevents errors
Me.txtTextBox = rs("Selgernavn") 'put "Selgernavn" in text box

End If

Else:

Me.txtTextBox = " " 'if no record exists, text box is blank

End If

Have fun!

Andrew
 
Back
Top