DLookup Help 1

[Lavenderchan]

I am trying to get dlookup to work but I'm unable to return anything in my form.
I have it set to update after event for project name. I want four fields fill in with the corresponding project information to save on data entry time. I have been plugging away at this for about 2 days now with no clue of why its not working. Here is my code
Private Sub Combo32_AfterUpdate()

REMS_Number = DLookup("[REMS Number]", "tblProperties", "[PropertiesID]=" & [PropertiesID])
fha_number = DLookup("[FHA Number]", "tblProperties", "[PropertiesID]=" & [PropertiesID])
contract_number = DLookup("[Contract Number]", "tblProperties", "[PropertiesID]=" & [PropertiesID])
Project_Manager = DLookup("[Project Manager]", "tblProperties", "[PropertiesID]=" & [PropertiesID])

End Sub
Any Help would be greatly appreciated.
Keri

 

[PHV]

I'd try this:
REMS_Number = DLookup("[REMS Number]", "tblProperties", "[PropertiesID]=" & Me!Combo32

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lavenderchan]

Hi PHV,

When I tried the &ME!Combo32. I get return a null.How do I get rid of the null.
Thanks,
Keri

 

[dhookom]

I would use a single combo box and some code. You could set these properties:

Name: cboPropertiesID
Control Source: PropertiesID
Row Source: SELECT PropertiesID, [REMS Number],[FHA Number], [Contract Number], [Project Manager] FROM tblProperties;
Column Count: 5

Then in your after update, you can set code like:

Me.REMS_Number = Me.cboPropertiesID.Column(1)
Me.fha_number =  Me.cboPropertiesID.Column(2)
Me.contract_number = Me.cboPropertiesID.Column(3)
Me.Project_Manager = Me.cboPropertiesID.Column(4)

You aren't storing values that can be looked up are you?


Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Is PropertiesID defined in the underlying table as Numeric or Text? It makes a difference in the syntax that's used.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Lavenderchan]

The propertiesid is numeric.

 

[Lavenderchan]

Hi I guess I should explain more of why I'm using dlookup.
I am tracking mail for my office. I have five fields that are for each record property name, rems number, fha number, contract number and project manager. I use these fields everytime I log a new piece of mail and assign the task. I was using combo boxes for each of the upabove mention fields but it takes to long plus I dont always have all of the above information when a letter is log. i just want to save some data entry time. I'm not having much luck with this. Is dlookup I shouldn't be using?
thanks,
Keri

 

[dhookom]

What is the Row Source of your combo box?
What is the Control Source of your combo box?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

the control source of my combo box is Property Name and the row source is SELECT [q_Open Items].[Property Name] FROM [q_Open Items] ORDER BY [Property Name];

Thanks,
Keri

 

[dhookom]

Does the query "q_Open Items" contain the fields [REMS Number],[FHA Number], [Contract Number], and [Project Manager]? Can it be modified to contain these columns?

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

the query contains all of this information. I just want to have after I enter the property name in its combo box to have the [REMS Number],[FHA Number], [Contract Number], and [Project Manager] fields populate. These numbers are property specific. They all have there own fields on the form. I've tried everything I can think of to make this work but I'm not having any luck.

 

[dhookom]

I'm not sure how/why/what you are using for [property name] vs [PropertiesID]. It's all very confusing since I would expect the combo box to return the ID.

However, you can try change the combo box properties:
[tt]
Row Source: SELECT [Property Name],[REMS Number],[FHA Number], [Contract Number], [Project Manager]
FROM [q_Open Items] ORDER BY [Property Name];
Column Count: 5
Bound Column: 1
Column Widths: 2,0,0,0,0
[/tt]
Then you can add code in the after update event of the combo box to:

Me.REMS_Number = Me.Combo32.Column(1)
Me.fha_number =  Me.Combo32.Column(2)
Me.contract_number = Me.Combo32.Column(3)
Me.Project_Manager = Me.Combo32.Column(4)

BTW: Combo32 isn't a good name for a combo box that you want to use in code or whatever.

Duane
Hook'D on Access
MS Access MVP

 

[Lavenderchan]

Thank you so very much. I can not express how much of a relief this to get this to work. I will study more in the future and not use numbers in my combo boxes.

Keri