Dlookup & Input box and OPen form

[Locopete99]

Hi,

Could you please look at my code below.

In a nutshell, I'm looking to cross reference a secondary "Continaution" reference with the original "Blanket Agreement" reference.

To Do this, I have a Dlookup to lookup the "Continuation ref" in Tbl_Cont (That has 2 fields - Continaution ref and Original shikomi reference)

This will then place the "original shikomi reference" into the "Where" condition of the Open Shikomi register form.

Dim BlanketAgreementNumber22 As Variant
Dim Continuation As String

Continuation = InputBox("Please Enter the Continuation Reference")



BlanketAgreementNumber22 = DLookup("[Shikomi Parent Number]", "Tbl_Cont", [Continuation Reference] = Continuation)




DoCmd.OpenForm "Frm_Shikomi_register", acNormal, "", "[BlanketAgreementNumber22]=" & "'" & [Blanket Agreement Number] & "'", , acNormal

 

[Locopete99]

Sorry, forgot to put - this isn't working!!!

PLease can someone see where I've gone wrong.

 

[Ranman256]

If your going to open the form with the key, then you don't need the DLOOKUP.

Continuation = inputBox.....
Docmd.openForm "form" ,,,"[field]=" & continuation

 

[Locopete99]

Not quite, the Continuation is linked to the Key, so the DLookup is there to lookup the key from the continuation

 

[Minty]

This syntax isn't correct you need to add the bits in red ;

BlanketAgreementNumber22 = DLookup("[Shikomi Parent Number]", "Tbl_Cont", [COLOR="red"]"[/COLOR][Continuation Reference] =[COLOR="red"] '"[/COLOR] & Continuation & [COLOR="Red"]"'"[/COLOR])

As a general rule it's a good idea to not have spaces or strange characters in your field names, it makes typing and referencing controls and fields a great deal simpler as you can lose all the square brackets.

 

[Locopete99]

Thanks Minty,

Adding the syntax has helped, now its pulling up the Dlookup with a data type mismatch?

As a primary Key, the Continuation ref is an Autonumber and the Shikomi Parent number is text

 

[Minty]

Ah but you have declared ;

Dim Continuation As String

So I assumed it was a string value. Remove the single quotes from the criteria. I personally would check that Continuation is a number in your code before doing the DLookup, otherwise your get the error the other way around if your user puts in text.

 

[Locopete99]

I had the Dim as String as I was using an Inputbox

Continuation = InputBox("Please Enter the Continuation Reference")

Is there a better way to do this as I still get a syntax if I take off the single quotes.

 

[Minty]

VAL(YourString) will return the number value. Or I'd probably code it something like

Dim iContinuation as Long

iContinuation = VAL(InputBox("Enter Continuation Ref"))