=DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

[Pedigreeman]

=DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

=DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])


This statement controls a textbox on a form. It looks up a colour ID number on the colours tale, and presents the name of that colour on the form. In many cases a colour ID has not been assigned to a particular record, in which case it returns an error. Rather than return an error, I want it to either do nothing, return a blank, or make the text box invisible. Whichever is easiest for a non-programmer.

How can I incorporate this response into the code?


Many thanks for your time


Mark

 

[Steve R.]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

=DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])


This statement controls a textbox on a form. It looks up a colour ID number on the colours tale, and presents the name of that colour on the form. In many cases a colour ID has not been assigned to a particular record, in which case it returns an error. Rather than return an error, I want it to either do nothing, return a blank, or make the text box invisible. Whichever is easiest for a non-programmer.

Look-up the NZ function in Access. I will assume that "not assigned" means that a NULL value is returned and not and empty string. Try:

lonResult=NZ(DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID]),-1)
if lonResult =>0 then Me.TextBox.Visible =True ELSE Me.Textbox.Visible=False

 

[Pedigreeman]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Look-up the NZ function in Access. I will assume that "not assigned" means that a NULL value is returned and not and empty string. Try:

lonResult=NZ(DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID]),-1)
if lonResult =>0 then Me.TextBox.Visible =True ELSE Me.Textbox.Visible=False

Thanks for your quick response. Yes, by 'not assigned' I meant that a Null value is returned which results in the error.

I created a dummy text box called 'TB1' to try out your code. In the 'on current' property of the form, I entered your code:

lonResult=NZ(DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID]),-1)
if lonResult =>0 then Me.TB1.Visible =True ELSE Me.TB1.Visible=False

When scrolling through the records on the form, the TB1 is blank for all records when it ought to be showing a colour name, and for those records where it ought to be blank it comes up with an error:
"syntax error (missing operator) in query expression '[colour_ID] = '."

Am I entering the code in the wrong place?

Is it possible to have a code to enter in the control source property of the text box that states: if the colour_ID for this record is null, then return blank, otherwise...DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

 

[bob fitz]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi Pedigreeman

In the table "Colours", what sort of field is [Colour_ID].

I think that Steve R. has assumed that it is a number because of the "ID" in its name. If it is actually a text field then the code you were given should include extra Apostrophes:

[/FONT]
[FONT=Times New Roman][SIZE=2]lonResult = Nz(DLookup("[Colour]", "Colours", "[Colour_ID] = '" & [Colour_ID] & "'"), -1)

[/SIZE]

 

[Pedigreeman]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi Pedigreeman

In the table "Colours", what sort of field is [Colour_ID].

I think that Steve R. has assumed that it is a number because of the "ID" in its name. If it is actually a text field then the code you were given should include extra Apostrophes:

[FONT=Times New Roman][SIZE=2]lonResult = Nz(DLookup("[Colour]", "Colours", "[Colour_ID] = '" & [Colour_ID] & "'"), -1)[/SIZE][/FONT]

Hi Bob,

Colour_ID is a number field (autonumber), and is the primary key of the table 'Colours'.

Any more ideas on this one?

 

[bob fitz]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi Pedigreeman

Have you set the Control Source property of your "TB1" text box?

 

[Pedigreeman]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi Pedigreeman

Have you set the Control Source property of your "TB1" text box?

No I left it blank/unbound. I wanted TB1 to simply display the colour of the underlying Colour_ID. Should I have set the control source to something?

 

[bob fitz]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi
Please try this:

lonResult=DLookUp("[Colour]","Colours","[Colour_ID] = " & NZ([Colour_ID]),0)
If lonResult >0 then 
  Me.TB1.Visible =True 
Else 
  Me.TB1.Visible=False
End If

 

[Pedigreeman]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi
Please try this:

lonResult=DLookUp("[Colour]","Colours","[Colour_ID] = " & NZ([Colour_ID]),0)
If lonResult >0 then 
  Me.TB1.Visible =True 
Else 
  Me.TB1.Visible=False
End If

Thanks Bob, ok I have now tried this and it came up with a Compile error: Wrong number of arguments or invalid property assignment.

Here is what I entered:

Private Sub Form_Current()
lonResult = DLookup("[Colour]", "Colours", "[Colour_ID] = " & Nz([Colour_ID]), 0)
If lonResult > 0 Then
  Me.TB1.Visible = True
Else
  Me.TB1.Visible = False
End If

I am starting to think that I should maybe just add a new colour 'blank' with a unique colour_ID and make sure that all records have at least this entered as their colour.

 

[Steve R.]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

To see your result in the textbox, the texbox needs a control source or code to display the result. See the modified code below.

lonResult=NZ(DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID]),-1)
if lonResult =>0 then Me.TB1.Visible =True : Me.TB1=lonResult ELSE Me.TB1.Visible=False

Also, does your form have a textbox "Colour_ID"? Does this that textbox have a value in it?

Learning to use dLookup is good, but it seems that you don't need to use it. Why not simply bind TB1 to the field "Colour"?

 

[Pedigreeman]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Thanks for your help. I tried the updated code and the text box is now showing the correct colour for each record, however it still comes up with the syntax (missing operator) error when I scroll to a record in which there is no data on colour. I am sure I have entered the code in the wrong place or something similar.

To see your result in the textbox, the texbox needs a control source or code to display the result.

What would the source contol be? This textbox needs to display the results without updating any fields. I currently left it blank and entered your code into the 'on current' property of the form. Am I entering your code into the correct place?

Also, does your form have a textbox "Colour_ID"? Does this that textbox have a value in it?

I tried adding a Colour_ID textbox and I am getting the same problem. The textbox displays whatever is in the Colour_ID field for that record (blank or a number).

Learning to use dLookup is good, but it seems that you don't need to use it. Why not simply bind TB1 to the field "Colour"?

This form is based on a table which contains the 'Colour_ID' field, but not the actual colour names field ('colour'). The table is linked to a lookup table called 'Colours' which contains all the colour names ('colour') corresponding to the Colour_ID. I suppose I could combine the two tables in a query (mapping 'colour_ID' and corresponding 'colour' fields together) and then create a new form based on this query, which would provide me with access to the 'colour' field on my form. This seems like a good back up plan, but I am curious to know where I am going wrong as I will without doubt make the same mistakes in the future.

Many thanks for your patience!

 

[bob fitz]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Hi

I suppose I could combine the two tables in a query (mapping 'colour_ID' and corresponding 'colour' fields together) and then create a new form based on this query, which would provide me with access to the 'colour' field on my form.

I think that would actually be the best way to do it, but I would also like solve the original problem. Now that you have a control source for the text box I think the code I posted earlier will work.

Private Sub Form_Current()
lonResult = DLookup("[Colour]", "Colours", "[Colour_ID] = " & Nz([Colour_ID]), 0)
If lonResult > 0 Then
  Me.TB1.Visible = True
Else
  Me.TB1.Visible = False
End If

 

[Steve R.]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

I tried the updated code and the text box is now showing the correct colour for each record, however it still comes up with the syntax (missing operator) error when I scroll to a record in which there is no data on colour. I am sure I have entered the code in the wrong place or something similar.

The problem may be that the field is an autoincrement field. You may be trying to access a record that has not yet been created. Set the form's "allow additions" property to "NO" as a test.

What would the source contol be? This textbox needs to display the results without updating any fields. I currently left it blank and entered your code into the 'on current' property of the form. Am I entering your code into the correct place?

Textboxes are useful for displaying data that should not be changed. This can be done by setting the textbox "lock" property to true. The control source for the textbox would be the "Colour_ID" field assuming that it is in the table that the form is bound to. Yes you are entering the code in the correct location.

This form is based on a table which contains the 'Colour_ID' field, but not the actual colour names field ('colour'). The table is linked to a lookup table called 'Colours' which contains all the colour names ('colour') corresponding to the Colour_ID. I suppose I could combine the two tables in a query (mapping 'colour_ID' and corresponding 'colour' fields together) and then create a new form based on this query, which would provide me with access to the 'colour' field on my form.

This would be a better approach as it would eliminate the need for the use of dLookup.

 

[Pedigreeman]

Re: =DLookUp("[Colour]","Colours","[Colour_ID] = " & [Colour_ID])

Many thanks for your help Steve, I have learned a few new things. In the end I did as you suggested and eliminated the need for the Dlookup.