Solved DLookup

[TajikBoy]

Good afternoon guys/gals,

I'm trying to assign a value to a text box on form load with below code but Access tells me I can't....
As usual, what am I doing wrong?

Also tried .value, same error.....

Me.Text981 = DLookup("[CompanyName]", "tblVendors", "[ID]=" & Me.VendorName)

 

[Ranman256]

This looks correct...what error msg?
Is the box name correct? Me.vendorname
where is the code? In form load? In other event?
is the return field name correct?

 

[TajikBoy]

This looks correct...what error msg?
Is the box name correct? Me.vendorname
where is the code? In form load? In other event?
is the return field name correct?

Hi,

Text981 - correct, me.vendorName correct

Code in Form Load....

If I change the vendorname combobox to a different vendor it works ?

 

[Ranman256]

What error?

 

[TajikBoy]

This looks correct...what error msg?
Is the box name correct? Me.vendorname
where is the code? In form load? In other event?
is the return field name correct?

 

[Ranman256]

Is ID a numeric field?
is the combo bound to the correct column?

 

[arnelgp]

does vendorname combobox has a value when the form load?
does Me.Text981 textbox has an expression in it as Control source?

 

[Ranman256]

Ah, the Text981 cannot be bound to a field.
it must be unbound

 

[TajikBoy]

Ah, the Text981 cannot be bound to a field.
it must be unbound

It is unbound..... It works on some vendors...... which is the puzzling bit

 

[TajikBoy]

does vendorname combobox has a value when the form load?
does Me.Text981 textbox has an expression in it as Control source?

Yes, it opens up from a list a view contract form sort of and yes, same Dlookup is in there too, as I need it to update if I change the vendor on the form

 

[arnelgp]

that is the problem, remove the expression on the Control Source.
and just add code to the AfterUpdate of the vendorname combobox
to fill the textbox again using Dlookup().

 

[Gasman]

You cannot assign a value to a control that has an expression as it's source?
I cannot see any vendor name working like that?. Plus if vendor name is a string, it should be surrounded by single quotes, or triple " if the string could have a single quote in it.

 

[TajikBoy]

OK..... Problem solved for erratic working.....

Apart from the expression error as mentioned by you guys, I found out that my data entry guy took a short cut and instead of linking the ID fields, he has just typed the vendor names, now he is correcting, after he will dig his own grave - is there a easier code to make him disappear???

Thanks a million, and for sure, I'll be back soon

 

[Pat Hartman]

Do you realize that the Load event runs only once when the form loads? If you ever navigate to a new record or use the form to add a record, the code won't run.

A better way to do this is to put an expression into the unbound CompanyName field' ControlSource.

= YourCompanyComboName.Column(1)

The RowSource of the combo is a zero based array so .Column(1) references the SECOND column of the RowSource. If the name is in a different column, Count the columns, 1,2,3,... then subtract 1.

And a warning " Me.Text981 " is not only a bad name for a control, it is indicative of an upcoming problem. There are a limited number of controls allowed on any form. You have apparently added and deleted lots of controls to get up to 981. I suggest an immediate C&R. I don't remember if that resets the count. You may have to create a new, empty database and import all the objects to avoid not being able to update the form any more. Access never forgets and this eventually causes a problem.

 

[June7]

If PK ID field is autonumber type then the FK field must be number type. How could names be typed into field? Your 'data entry guy' is a user? Why would a user be setting up links?

 

[TajikBoy]

Do you realize that the Load event runs only once when the form loads? If you ever navigate to a new record or use the form to add a record, the code won't run.

A better way to do this is to put an expression into the unbound CompanyName field' ControlSource.

= YourCompanyComboName.Column(1)

The RowSource of the combo is a zero based array so .Column(1) references the SECOND column of the RowSource. If the name is in a different column, Count the columns, 1,2,3,... then subtract 1.

And a warning " Me.Text981 " is not only a bad name for a control, it is indicative of an upcoming problem. There are a limited number of controls allowed on any form. You have apparently added and deleted lots of controls to get up to 981. I suggest an immediate C&R. I don't remember if that resets the count. You may have to create a new, empty database and import all the objects to avoid not being able to update the form any more. Access never forgets and this eventually causes a problem.

HI Pat,

Yes I do, that was a feeble attempt to resolve an issue caused by something else, which I have fixed now, also the bad name issue, form was copied over from a different project and modified, hence the 981......

All good now and value your advice.....

Thanks,

 

[Pat Hartman]

Glad to hear it. You're welcome