Results 1 to 8 of 8
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    Type mismatch error using Dlookup

    I have a form with an series of unbound textbox controls that I want to populate with field values from a foreign table. I am using Dlook up to retrieve the values. If there is a value, I want the checkbox next to the text box to be visible, otherwise it is not. The code works fine when the foreign table field value is not null. However, when the field is empty, I get a type mismatch error. Here is my code:
    Code:
    Private Sub Form_Load()On Error GoTo Err_Form_Load
    
    
    DoCmd.GoToRecord , , acNewRec
    Dim frm As Object
    Set frm = Forms!New_Contact_Entry!Add_Contribution_SubForm.Form
    frm.Text90.SetFocus
    frm.Text90.Text = DLookup("[Restricted_Use_Custom1]", "Organization_Info")
    If IsNull(frm.Text90.Value) = True Then
      frm.Check1.Visible = False
      End If
    I've tried multiple permutations of the If statement but I have no luck. Setting a default value in the foreign table isn't working either.

    Thanks

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When you have a "foreign table" and want to see field(s) from that table on a form, you could try:

    -creating a database
    -link to the foreign table (Linked Table Manager)
    -create or use form wizard to autocreate a form based on the table

    It would be helpful if we knew more about the "foreign table"

    Info on Linked Table manager

    Good luck

  3. #3
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Maybe I'm using the wrong terminology. The "foreign table" is actually a table currently in my database; I just didn't have it set as the record source for the form to keep things simple.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For learning/practice:
    Try creating a new form;
    Use the Form wizard
    Use the table when asked by the wizard.

    Just experiment and see how access creates a bound form. It's the simplest way.

    Good luck

  5. #5
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Sorry, That isn't much help. My database consists of 3 tables, 2 are linked in a 1 to many relationship ("Contact info" and "donations", respectively), the third, "organizational info" is not linked to the other 2. I have a "contact entry" form for entering demographic data on individuals. Records from this form are saved to the "contact info" table. A subform is linked to the "contract entry" form for the purpose of entering financial contributions. Data from this subform is saved to the "donations" table. I want to get a field value from the "organizational info table" and place that value in a textbox on my subform. My code that I posted in the initial post works as long as the field value in the organization table is not empty. Type mismatch occurs otherwise.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Your Dlookup has no criteria, so it will always find the first record in Organization_Info.
    To overcome the type mismatch try
    Code:
    frm.Text90 = NZ(DLookup("[Restricted_Use_Custom1]", "Organization_Info"))

  7. #7
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    there shouldn't be brackets [ ]

    frm.Text90 = NZ(DLookup("Restricted_Use_Custom1", "Organization_Info"))

    this assumes it is a single record query - since there is no criteria as per prior quote....

  8. #8
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Thanks, that worked!. Since the table "organization info" only has a single record I left the brackets in. I appreciate the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DLookup type Mismatch
    By S1naps1s in forum Programming
    Replies: 3
    Last Post: 11-26-2014, 06:09 PM
  2. DLookup Type Mismatch Error
    By DCV0204 in forum Forms
    Replies: 2
    Last Post: 02-11-2014, 01:54 PM
  3. VBA DLookup - Type Mismatch
    By hannuk in forum Access
    Replies: 2
    Last Post: 01-03-2012, 06:33 AM
  4. Newbie. DLookup gives type mismatch error.
    By graffiti in forum Programming
    Replies: 4
    Last Post: 12-13-2011, 05:22 AM
  5. DLookup not recognizing valid record/type mismatch
    By walter189 in forum Programming
    Replies: 3
    Last Post: 07-21-2011, 07:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums