asked on

dlookup error

With objWord
'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Date").Select 'Date
.Selection.Text = (CStr(Forms!Quote!Dateofcontact))
.ActiveDocument.Bookmarks("CN").Select 'Organisation Name
.Selection.Text = (CStr(DLookup("Organisation", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]")))
.ActiveDocument.Bookmarks("CN1").Select 'Organisation Name
.Selection.Text = (CStr(DLookup("Organisation", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]")))
.ActiveDocument.Bookmarks("Price").Select 'Price
.Selection.Text = (CStr(Forms![Quote]![Text116]))

If Forms![Quote]![quote_sub_quote]![Publicholidays] = -1 Then
.ActiveDocument.Bookmarks("ph").Select 'Public Holidays
.Selection.Text = (CStr("inclusive"))
Else
.ActiveDocument.Bookmarks("ph").Select 'Public Holidays
.Selection.Text = (CStr("exclusive"))
End If

.ActiveDocument.Bookmarks("SRB").Select 'Standard Rubbish bags
.Selection.Text = (CStr(DLookup("Cost", "Materials", "[Material No] = 94")) * 100 * 1.5)
.ActiveDocument.Bookmarks("DocN").Select 'Document Number
.Selection.Text = (CStr(Forms!Quote!CommunicationID))
.ActiveDocument.Bookmarks("BL").Select 'Standard Rubbish bags
.Selection.Text = (CStr(DLookup("Cost", "Materials", "[Material No] = 522")) * 100 * 1.5)
.ActiveDocument.Bookmarks("DPW").Select 'Days per week
.Selection.Text = (CStr(Forms![Quote]![quote_subSUB_sumhrs]![NoDays]))
.ActiveDocument.Bookmarks("Street1").Select 'Street Address 1
.Selection.Text = (CStr(DLookup("Street", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]")))
.ActiveDocument.Bookmarks("Street2").Select 'Suburb
.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = DLookup(Suburb, Organisation, OrganisationID = Forms![quote]![OrganisationID])")))
Word.ActiveDocument.ActiveWindow.View.Type = wdPrintView

###

Im getting an error on this line:
.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = DLookup(Suburb, Organisation, OrganisationID = Forms![quote]![OrganisationID])")))

Error 2001
You cancelled the previous operation

walterecook

Try getting your dlookups working independently. That is if you stop the first piece here:
(CStr(DLookup("Description", "Types", "[ID] = 9")
Does it work?

I can see this piece is broken
DLookup(Suburb, Organisation, OrganisationID = Forms![quote]![OrganisationID])") It needs to be
DLookup("Suburb", "Organisation", "[OrganisationID] = " & Forms![quote]![OrganisationID])

I suggest getting it working too THEN you can combine them into something like:
CStr(DLookup("Description", "Types", "[ID] = " & DLookup("Suburb", "Organisation", "[OrganisationID] = " & Forms![quote]![OrganisationID])))

Good luck
Walt

Mike Eghtebas

.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = " & DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]))

Is this lookup returning as value compatible with ID (are they both numbers)?

They have to be same. If they are string use:

.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = " & chr(34) & DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]) & chr(34))

MIke

robiago

ASKER

hey Walt

.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = 9")))

works

When I try:
.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]")")))

it gives me a syntax error on suburb says it expects a list seperator or )

I dont see why I would need &

Rob

walterecook

That's just the way it works. I'd suggest getting the second piece working before trying to combine them.

Here a recent example related to your problem:
https://www.experts-exchange.com/questions/20791855/DLookup-Question.html

Walt

Mike Eghtebas

try:

.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = " & DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]))

if Suburb is a number. See my last post for more...

Mike

robiago

ASKER

hey Mike,

I tried that and it gave me the same error: expects a list seperator or )
but it didnt highlight anything and yes suburb is a number

I'll double check they are both the same

Rob

Mike Eghtebas

You never veryfied whether ID and Suburb have the same property or not. Do they have same property?

Try:

.Selection.Text = CStr(DLookup("Description", "Types", "[ID] = " & DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]))

walterecook

Mike
So you know
Your code is missing a " at the end. You only have 11 total.

Thought I'd could help
Walt

robiago

ASKER

they do have the same property, suburb looks up ID in types

robiago

ASKER

same error mike

Mike Eghtebas

Thanks walt.
--------

To paly safe, lets try this first.

---------------code starts here

Dim IdVar
MsgBox "1: VarType= " & Vartype(Forms![quote]![OrganisationID]) "; " & Forms![quote]![OrganisationID]

IdVar =DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]
MsgBox "2: VarType= " & Vartype(IdVar) "; " & IdVar

.Selection.Text = CStr(DLookup("Description", "Types", "[ID] = " & IdVar)

---------------code ends here

let me know, using above code, what you get for msgbox 1 and 2.

Mike

robiago

ASKER

sorry mike i wasnt born a coder :/
it says syntax error on these lines:
MsgBox "1: VarType= " & Vartype(Forms![quote]![OrganisationID]) "; " & Forms![quote]![OrganisationID]

IdVar =DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]
MsgBox "2: VarType= " & Vartype(IdVar) "; " & IdVar

.Selection.Text = CStr(DLookup("Description", "Types", "[ID] = " & IdVar)

Rob

walterecook

Mike
I'm curious why your 2 strands are different styles:
dlookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]")
DLookup("Description", "Types", "[ID] = " & IdVar)
see the difference?
One has " and &
one doesn't
Personally I think this is the problem.

Walt

walterecook

Try Mike's lines this way:
MsgBox "1: VarType= " & Vartype(Forms![quote]![OrganisationID]) & "; " & Forms![quote]![OrganisationID]

IdVar =DLookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]")
MsgBox "2: VarType= " & Vartype(IdVar) & "; " & IdVar

Just missing a quote or and & here and there. It happens.

Mike Eghtebas

Hi Rob,

I suspect code can't read OrganisationID from your form. To check that, try follwing instead:

MsgBox "1: VarType= " & nz(Forms![quote]![OrganisationID],"can't read OrganisationID")

------
Hi Walt,

we can write:
dlookup("Suburb", "Organisation", "OrganisationID = Forms![quote]![OrganisationID]") or
dlookup("Suburb", "Organisation", "OrganisationID =" & Forms![quote]![OrganisationID])
they both will work, but we don't have that option with:
DLookup("Description", "Types", "[ID] = " & IdVar)

Mike

robiago

ASKER

it came back:
1: Vartype = 3; 12
2: Vartype = 3; 17

Mike Eghtebas

Rob,

It gave you the error because after update of OrganisationID wasn't fired and couldn't get its value (I suspect). If this happens again, we need to handle it somehow.

Do you the solution now?

Mike

SOLUTION

walterecook

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robiago

ASKER

OK:
OrganisationID = Forms![quote]![OrganisationID]
the first organisation ID is located in the Organisation table the second is located in the form quote
which one is the problem?

Mike Eghtebas

The one you have (or enter data into), OrganisationID on form quote.

Forms![quote]![OrganisationID]

I don't know what table it is linked to. BTW, is it is linke, it shouldn't problem unluss it is a new data entry (new record) and OrganisationID value is not there yet.

Is text box Forms![quote]![OrganisationID] unbound?

Mike

robiago

ASKER

its linked to the organisation table

SELECT Organisation.OrganisationID, Organisation.Organisation FROM Organisation;

Mike Eghtebas

What kind of proble you are having as is stands. For now, keep follwing line active until you are sure it doesn't return null values for OrganisationID:

MsgBox "1: VarType= " & nz(Forms![quote]![OrganisationID],"can't read OrganisationID")

Mike

robiago

ASKER

I think the problem is that I have a table called Quote and a form called quote and that its seeing Forms! as a field and not as something telling it to please look @ forms - quote - org id

Mike Eghtebas

For your future consideration:

Quote
for table call it tQuote
for form call it fQoute
for subform call it sfQoute
for query call it qQoute
Primary keys call it CustID
Foreing Keys call it Cust_ID
------------

As far as your recent question is concerend, past the portion of the code is doing that.

Mike

robiago

ASKER

thanks mike :D

robiago

ASKER

I tried:
MsgBox "1: VarType= " & nz(Forms![quote]![OrganisationID],"can't read OrganisationID")

came back with Vartype = 12

Mike Eghtebas

My bad, use:

MsgBox "Current value of OrganisationID is: " & nz(Forms![quote]![OrganisationID],"can't read OrganisationID") & " remove this message box after number of successful test."

robiago

ASKER

OK the msg box was working and Ive removed it

.Selection.Text = (CStr(DLookup("Description", "Types", "[ID] = & DLookup(Suburb, Organisation, OrganisationID = Forms![quote]![OrganisationID])")))

I've taken the " " out of the last dlookup because it kept giving me an error: expects a list seperator or )

and as it is now I get this error:
3075:
syntax error (missing operator) in query expression 'ID = & DLookup(Suburb, Organisation, OrganisationID = Forms![quote]![OrganisationID])'.

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robiago

ASKER

that worked! :D mint thanks so much

Mike Eghtebas

Walt,

https://www.experts-exchange.com/questions/20796224/Points-for-walt.html

Mike