Access Form Extract a table value based on form field

jbakestull
jbakestull used Ask the Experts™
on
I"m just having a hard time trying to extract a value from a table via SQL.  

cbotest is a combo box field located on frmUSHS.

After the updated event if the client (intCSPIdentifier from tblVulnerabilityIndex = cbotest) and has a score (intTotalScore from tblVulnerabilityIndex), I would to populate the results on a text field call in USHS from called "txtVulscore".

Any help would be grateful. My head is starting to hurt trying to find a syntax sample that I can use.

Thanks


Table
tblVulnerabilityIndex
intCSPIdentifier
intTotalScore

Form
frmUSHS
cbotest
txtVulscore


Dim rs As Recordset
    Dim strSQL As String
    strSQL = “select [intTotalScore] from tblVulnerabilityIndex where intCSPIdentifier=' & Me.cbotest & "    Set rs = CurrentDb.OpenRecordset(strSQL)
    If Not rs.EOF Then
     
    End If
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Dim rs As Recordset
    Dim strSQL As String
    strSQL = “select [intTotalScore] from tblVulnerabilityIndex where intCSPIdentifier=" & Me.cbotest & " And [intTotalScore] <> Null"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If Not rs.EOF Then
        me.txtVulscore=rst![intTotalScore]
    End If


you can also use Dlookup() if you want to.

me.[intTotalScore]=dlookup("intTotalScore","tblVulnerabilityIndex","intCSPIdentifier=" & Me.cbotest & " And [intTotalScore] <> Null")

Author

Commented:
Thank you. You made my day.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial