?
Solved

DLookup - if exists, display me!

Posted on 2005-04-28
8
Medium Priority
?
289 Views
Last Modified: 2008-02-01
I have a form with a textbox called 'ServiceID'.

Each time my form opens I'd a like a lookup to peek at a query called 'query34' - and if the value of 'ServiceID' appears in the column called 'ServiceID' I'd like a textbox called 'txtTick' to be visible, otherwise not visible.

Easy or not?  

Thanks
Lapchien

0
Comment
Question by:Lapchien
  • 4
  • 3
8 Comments
 
LVL 14

Expert Comment

by:JohnK813
ID: 13885224
How many records (rows) does query34 have?  If there is only one, you can use DLookup.

Private Sub Form_Load()
  If (DLookup("[ServiceID]", "query34") = "ServiceID") Then
    txtTick.Visible = True
  Else
    txtTick.Visible = False
  End If
End Sub
0
 

Author Comment

by:Lapchien
ID: 13885240
Query34 has 5051 rows...

0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 13885289
Easy!

Private Sub Form_Open(Cancel As Integer)
    Dim n As Integer
    Dim id As String
    id = Me.ServiceID.Value
    n = Nz(DLookup("ServiceID", "query34", "ServiceID=" & id), 0)
    txtTick.Visible = (n > 0)
End Sub

My only concern is that the ServiceID text box probably won't have a value when the form is opened, unless you're assigning a default value.

-- Craig Yellick
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Lapchien
ID: 13885360
hmmm. ok.  ServiceID is text.
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 13885404
>> "ServiceID is text"

Do you mean the underlying data type in the table is text? If so, the DLookup will need quotes around the ID value:

  n = Nz(DLookup("ServiceID", "query34", "ServiceID='" & id & "'"), 0)

How is the ServiceID text box value being assigned? Does the user enter something, or do you pass an ID during the opening of the form?  In order to test the above solution I had to manually set the default value, then close and re-open the form to make it work.

-- Craig Yellick
0
 

Author Comment

by:Lapchien
ID: 13885526
Difficult.  Here's what I've done - on the main form created a subform from the query and joined using ServiceID.  I just need some code now to say:

If <subformfield> on <subformname> is not null, then me.button.visible = true, otherwise me.button.visible = false

What would the code be for that?

Thanks
 
0
 
LVL 11

Accepted Solution

by:
Craig Yellick earned 2000 total points
ID: 13885651
The syntax for referencing controls in a subform is ugly but it works.

Private Sub Form_Current()
    Dim value As Variant
    value = Forms![YourFormName]!subData.Form![YourFieldName]
    me.cmdYourButtonName.Visible = Not IsNull(value)
End Sub

-- Craig Yellick
0
 
LVL 11

Expert Comment

by:Craig Yellick
ID: 13885665
I should mention that, in the above example, the name of the subform control on YourFormName is subData, which is almost certainly not what you named yours.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question