Retrieve info from table

[Zulay]

Good afternoon all:

I have a table with 3 columns: ID, Name, Profession. In one of my forms,
I have a textbox with a name of a person that exists on the table. I would
like to retrieve the profession of that person by looking in the table and
save the profession in a variable to use it later in an if else statement. I
understand the algorithm but I am not sure how to write it on Visual Basic.

This is the code of what I want to do but without implementing what I
describe above. I need help on that.

Dim stDocName As String
Dim stLinkCriteria As String
Dim personfuntion As String

personprofession = ........?

If (personprofession = "Engineer") Then

stDocName = "Form1"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help will be really appreciated. Thanks!

 

[Ken Sheridan]

You can use the DLookup function to get the value from the Profession column
in the table. I'm assuming you want to open the form at the record for the
person in question, in which case the code would be like this:

Dim stDocName As String
Dim stCriteria As String
Dim stProfession As String

stCriteria = "[Name] = """ & Me.txtName & """"
stProfession = DLookup("Profession, "YourTable", stCriteria)

If stProfession = "Engineer" Then
stDocName = "Form1"
DoCmd.OpenForm stDocName, WhereCondition:=stCriteria
ElseIf
<more code>
End If

where txtName is the name of the text box on the form and YourTable is the
name of the table.

However, names can be duplicated, so I'd suggest a different method using a
combo box whose value is the unique numeric ID, rather than a text box. Set
up the combo box as follows:

RowSource: SELECT ID, [Name], Profession FROM YourTable ORDER BY [Name]

BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;4cm;4cm
ListWidth: 8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the ColumnWidths and ListWidth dimensions. The
important thing is that the first dimension is zero to hide the first column
and that the second is at least as wide as the combo box. Experiment to get
the best fit. The ListWidth property should be the sum of the ColumnWidths.

The code would now be:

Dim stDocName As String
Dim stCriteria As String
Dim stProfession As String

stCriteria = "ID = " & Me.cboName
stProfession = Me.cboName.Column(2)

If stProfession = "Engineer" Then
stDocName = "Form1"
DoCmd.OpenForm stDocName, WhereCondition:=stCriteria
ElseIf
<more code>
End If

In this case the name is derived from the combo box's Column property. This
is zero-based, so Column(2) is the third column, i.e. Profession.

Ken Sheridan
Stafford, England

 

[Zulay]

Thanks for your help Ken. I create a combo box and set the default value to
the value of another combo box I have on a previous form. The default value
corresponds to the name of a person.

I got an error in the following piece of code:

stCriteria = "ID = " & Me.cboName
stProfession = Me.cboName.Column(2)

Me.cboName.Column(2) value is null. I noticed that if I click the combo and
choose a person from the list, it works... but since the name of the person
is retrieve from a combo box in another form, what can I do? Please advice.
Thanks Again.

You can use the DLookup function to get the value from the Profession column
in the table. I'm assuming you want to open the form at the record for the
person in question, in which case the code would be like this:

Dim stDocName As String
Dim stCriteria As String
Dim stProfession As String

stCriteria = "[Name] = """ & Me.txtName & """"
stProfession = DLookup("Profession, "YourTable", stCriteria)

If stProfession = "Engineer" Then
stDocName = "Form1"
DoCmd.OpenForm stDocName, WhereCondition:=stCriteria
ElseIf
<more code>
End If

where txtName is the name of the text box on the form and YourTable is the
name of the table.

However, names can be duplicated, so I'd suggest a different method using a
combo box whose value is the unique numeric ID, rather than a text box. Set
up the combo box as follows:

RowSource: SELECT ID, [Name], Profession FROM YourTable ORDER BY [Name]

BoundColum: 1
ColumnCount: 3
ColumnWidths: 0cm;4cm;4cm
ListWidth: 8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the ColumnWidths and ListWidth dimensions. The
important thing is that the first dimension is zero to hide the first column
and that the second is at least as wide as the combo box. Experiment to get
the best fit. The ListWidth property should be the sum of the ColumnWidths.

The code would now be:

Dim stDocName As String
Dim stCriteria As String
Dim stProfession As String

stCriteria = "ID = " & Me.cboName
stProfession = Me.cboName.Column(2)

If stProfession = "Engineer" Then
stDocName = "Form1"
DoCmd.OpenForm stDocName, WhereCondition:=stCriteria
ElseIf
<more code>
End If

In this case the name is derived from the combo box's Column property. This
is zero-based, so Column(2) is the third column, i.e. Profession.

Ken Sheridan
Stafford, England

Good afternoon all:

I have a table with 3 columns: ID, Name, Profession. In one of my forms,
I have a textbox with a name of a person that exists on the table. I would
like to retrieve the profession of that person by looking in the table and
save the profession in a variable to use it later in an if else statement. I
understand the algorithm but I am not sure how to write it on Visual Basic.

This is the code of what I want to do but without implementing what I
describe above. I need help on that.

Dim stDocName As String
Dim stLinkCriteria As String
Dim personfuntion As String

personprofession = ........?

If (personprofession = "Engineer") Then

stDocName = "Form1"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Any help will be really appreciated. Thanks!

 

[Zulay]

Problem solved! The error was prompted because the value it was getting from
the previous combo box was the name only cause the previous combo only had to
columns: ID and Name. So I add to the previous combo box a third column
corresponding to the function of the person and it work. Thanks a lot for
help!