Dlookup

[Fiachra]

Hi

Iam trying to validate a text box, the user will enter a trainer name and I
want to make sure that the trainer exists in the workshop table. Instead of
comparin the name entered to the column trainer in the workshop table it
replace the name of the trainer in the first workshop record in the table
with the name entered by the user

Name = "X"

Name= DLookup("[Trainer]", "[WorkShop]", "[Trainer]='" & Me![Trainer]&
"'") & ""

If Name = "" Then
MsgBox "WorkShop Does not Exist, Please Try Again"
Trainer = ""
Exit Sub
End If

If you can have a look at my code above and see if there is anything wrong
that would be great.

Thanks
Fiachra

 

[Dirk Goldgar]

Hi

Iam trying to validate a text box, the user will enter a trainer name
and I want to make sure that the trainer exists in the workshop
table. Instead of comparin the name entered to the column trainer in
the workshop table it replace the name of the trainer in the first
workshop record in the table with the name entered by the user

Name = "X"

Name= DLookup("[Trainer]", "[WorkShop]", "[Trainer]='" &
Me![Trainer]& "'") & ""

If Name = "" Then
MsgBox "WorkShop Does not Exist, Please Try Again"
Trainer = ""
Exit Sub
End If

If you can have a look at my code above and see if there is anything
wrong that would be great.

This doesn't sound like a problem with the code per se, but rather that
the text box you're entering it into is bound to the field (and,
presumably, the form is bound to the table, or to a query includes that
table). So whatever you enter in that text box is going to be updating
a record -- whatever record is current on the form.

What is it you actually intend to do with this text box and form? That
will determine the best solution.

 

[Fiachra]

I want to take in the name that is entered in the box and return all the
workshops that trainer has created, but I would like to make sure the trainer
exists first

Hi

Iam trying to validate a text box, the user will enter a trainer name
and I want to make sure that the trainer exists in the workshop
table. Instead of comparin the name entered to the column trainer in
the workshop table it replace the name of the trainer in the first
workshop record in the table with the name entered by the user

Name = "X"

Name= DLookup("[Trainer]", "[WorkShop]", "[Trainer]='" &
Me![Trainer]& "'") & ""

If Name = "" Then
MsgBox "WorkShop Does not Exist, Please Try Again"
Trainer = ""
Exit Sub
End If

If you can have a look at my code above and see if there is anything
wrong that would be great.

This doesn't sound like a problem with the code per se, but rather that
the text box you're entering it into is bound to the field (and,
presumably, the form is bound to the table, or to a query includes that
table). So whatever you enter in that text box is going to be updating
a record -- whatever record is current on the form.

What is it you actually intend to do with this text box and form? That
will determine the best solution.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I want to take in the name that is entered in the box and return all
the workshops that trainer has created, but I would like to make sure
the trainer exists first

The simplest solution would be to use a combo box instead of a list box,
and set up the combo's row source so that it returns only the names of
existing trainers. You'd probably set the combo box's LimitToList
property to Yes, so it wouldn't accept the entry of any trainer who
wasn't in the list. If you do that, you don't need any code to "look
up" the trainer, because the combo has done that for you.

To repeat what I said in an earlier post, this control -- whether it's a
text box or a combo box -- should not be bound to a field. I presume
you're going to use it either to filter the current form, or to open
some other form to show the related records, or as the Link Master Field
for a subform. If I understand what you're trying to do, you do not
want this control to update any data; you just want to use it to control
the selection of data.

 

[Fiachra]

yeah I have made the field unbound and it seems to work ok now thanks
for your help