Picking out a specific record in VBA

[Eacaw]

Is there anyway for me to (In VBA) select a specific record from a specific table?

I need to be able to pick out the Employee Grade record from a predefined Employee Name. Basically, I have a drop down list on the form from which you select the employee that you want to view the details of then clicking a "load data" button it puts their name into a text box, I need to somehow use this information in the text box as criteria for a record search. So it looks for that Employee and then takes their GradeID and lets me use it in my code (will be writing it to a variable straight away)

Is there a way to do this? If so, How?

Dave

 

[CBrighton]

You can use a VBA recordset or a DLookup to do this.

 

[suburbanpsyco]

Is there anyway for me to (In VBA) select a specific record from a specific table?

I need to be able to pick out the Employee Grade record from a predefined Employee Name. Basically, I have a drop down list on the form from which you select the employee that you want to view the details of then clicking a "load data" button it puts their name into a text box, I need to somehow use this information in the text box as criteria for a record search. So it looks for that Employee and then takes their GradeID and lets me use it in my code (will be writing it to a variable straight away)

Is there a way to do this? If so, How?

Dave

Dim someVariableName as new ADODB.Connection
Dim someVariableName2 as new ADODB.Recordset

On form load:

set someVariableName = currentproject.connection
somevariablename2.open "Select * from yourTable", someVariableName, adOpenKeyset, adLockOptimistic, adCmdText

Now in order for this to work, you must have the the id of the employee bound to the box you select the name from.

someVariableName2.MoveFirst
someVariableName2.find "EmployeeID = " & me.yourtextbox

now declare variable = someVariableName2!grade field

*NOTE* this will work in access 2007 and up, but im not sure about previous versions

 

[The_Doc_Man]

Create the combobox that selects the name while the control wizard is enabled. When it asks you what you want to do with the selection, one of the options is "find that record in a table." Let Access build the code for you.

 

[Eacaw]

I've solved this probelm now, Have just opened up a form and had access put the data that I was looking for into text boxs so I can reference them in my code, however, my code is not working. There's no error messages coming up, but its just not doing what I want it to....

'Variables
Dim grade As String
Dim dept As String
Dim Hours As String
Dim CounterG As Integer
Dim CounterD As Integer
Dim Colour As String
Dim FinishedG As Integer
Dim FinishedD As Integer
'PreDefinition
grade = GradeID.Text
dept = DeptID.Text
CounterG = 0
CounterD = 0
FinishedG = 0
FinishedD = 0
'GradeFinder
Do
If GradeColour(0, CounterG) = grade Then
    FinishedG = 1
Else
    CounterG = CounterG + 1
End If
Loop Until FinishedG = 1
grade = CounterG
'DeptFinder
Do
If DeptColour(0, CounterD) = grade Then
    FinishedD = 1
Else
    CounterD = CounterD + 1
End If
Loop Until FinishedD = 1
dept = CounterD
'Reset Counters
CounterG = 1
CounterD = 1
FinishedD = 0
'Find Colour Match
Do
    If GradeColour(GounterG, grade) = DeptColour(CounterD, dept) Then
        Colour = GradeColour(CounterG, grade)
        FinishedD = 1
    Else
        CounterD = CounterD + 1
        CounterG = CounterG + 1
    End If
Loop Until FinishedD = 1
'End of Code and Output
ColReqText.Value = Colour
ColReq.Caption = Colour
MsgBox (Colour)
End Sub

If you want to understand better what it is doing goto the "Multiple Tables Query" thread in the "Queries" subforum. (Low post count doesn't let me post links)