Cannot get a DLookUp to work (1 Viewer)

F

fedebrin

Registered User.

Local time

Yesterday, 19:26

Joined

Sep 20, 2017

Messages

59

• Sep 18, 2018

• #1

The objective is to populate the "OrderDetails" form / table with data from the "Services" & "Items" table.

I am able get data from the "Items" table with the below event:

Private Sub ItemID_AfterUpdate()
Me![Brand] = DLookup("Brand", "Items", "[ItemID] = " & Nz(Me.ItemID, 0))
End Sub

I can get the "Order Details" populated with the "Brand" with no issue.


When I try to obtain the price by entering the "ServiceNameID", I get the syntax error.

This is the event:

Private Sub ServiceNameID_AfterUpdate()
Me![Price] = DLookup("Price", "Services", "[ServiceNameID] = " & Nz(Me.ServiceNameID, 0))
End Sub

The format of the fields are all the same so not sure why it is not working.

See relationship file for the structure of the DataBase

thanks!

 

Attachments

• 
  Relationships.JPG

  54.9 KB · Views: 82

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 19:26

Joined

Aug 30, 2003

Messages

36,124

• Sep 18, 2018

• #2

I'd use this if appropriate:

http://www.baldyweb.com/Autofill.htm

Is ServiceNameID a numeric field, or text?

 

F

fedebrin

Registered User.

Local time

Yesterday, 19:26

Joined

Sep 20, 2017

Messages

59

• Sep 18, 2018

• #3

Hi Paul,

It is Short Text.

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 19:26

Joined

Aug 30, 2003

Messages

36,124

• Sep 18, 2018

• #4

Then you need delimiters:

http://theaccessweb.com/general/gen0018.htm

But I'd still use the other method, as it involves fewer calls to the data.

 

F

fedebrin

Registered User.

Local time

Yesterday, 19:26

Joined

Sep 20, 2017

Messages

59

• Sep 18, 2018

• #5

Thanks, Paul, yes I see your point... basically I do not need to populate all the other fields on to the "OrderDetails"

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 19:26

Joined

Aug 30, 2003

Messages

36,124

• Sep 18, 2018

• #6

I would probably populate price, since it changes over time and you need to save the price each transaction used. Brand and color don't sound like things that I'd save in the details table, since presumably they don't change.

 

F

fedebrin

Registered User.

Local time

Yesterday, 19:26

Joined

Sep 20, 2017

Messages

59

• Sep 18, 2018

• #7

I still cannot get the delimeter right.

WOuld it be something like this?

Code:Copy to clipboard

Private Sub ServiceNameID_AfterUpdate()
Me![Price] = DLookup("Price", "Services", "[ServiceNameID] = 'Me![ServiceNameID]'")
End Sub

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 19:26

Joined

Aug 30, 2003

Messages

36,124

• Sep 18, 2018

• #8

No, more like:

Me![Price] = DLookup("Price", "Services", "[ServiceNameID] = '" & Me![ServiceNameID] & "'")

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 19:26

Joined

Aug 30, 2003

Messages

36,124

• Sep 18, 2018

• #9

And if it wasn't clear, the link included a method to populate price from the item dropdown, which would avoid the second trip to the data the DLookup() is making. Not a major issue, but it can help speed up apps.