Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    157

    Dlookup and unbound Forms and txt[fields]

    I can’t get Dlookup to work on an unbound form.

    ________________________________________________
    Firstly, just to give you a bit of back ground:
    I have a form called StaffTrainingF which uses Dlookups on a bound form and works perfectly. You can read what I have done to make this work for me below (this is to show that I can get it to work on a bound form) or you can skip this and go down the page to where I start talking about MY PROBLEM.

    The StaffTrainingF form is bound to a Table called StaffTraining as its record. The form allows Edits and Additions but no Deletions.
    When the form opens up it goes to the next new record (utilizing a macro on the forms OnOpen Properties).

    The form has the following fields:
    ***
    SSDetailsID is bound to a control source SSDetailsID but uses the following select query to help with the selection of the correct ID. It is written as:

    SELECT [aStaffSiteDetailsQ].[ID], [aStaffSiteDetailsQ].[StaffID], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[SiteCode], [aStaffSiteDetailsQ].[Site], [aStaffSiteDetailsQ].[RoleTitle], [aStaffSiteDetailsQ].[StaffActInact] FROM aStaffSiteDetailsQ ORDER BY [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[Site];

    It also has an On Click Event Procedure, which has the following code:

    Private Sub SSDetailsID_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    Me!StaffID.Value = Me!StaffIDLU.Value
    End Sub

    (this forces the following lookup fields to be refreshed every time there is an on click event. It also sets the Staff ID.Value to = the StaffIDLU Value so that it can be stored in the StaffTraining Table)

    ***
    StaffID has a control source of StaffID

    *** The following are Dlookups

    Staff1stNameLU
    =DLookUp("[Staff1stName]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    ***
    StaffLastNameLU
    =DLookUp("[StaffLastName]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    ***
    RoleTitleLU
    =DLookUp("[RoleTitle]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    ***
    StaffActInactLU
    =DLookUp("[StaffActInact]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    ***
    SiteCodeLU
    =DLookUp("[SiteCode]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    ***
    SiteLU
    =DLookUp("[Site]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    ***
    StaffIDLU
    =DLookUp("[StaffID]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingF]![SSDetailsID]")

    THIS ALL WORKS PERFECTLY
    ________________________________________________





    MY PROBLEM is with using Dlookups on an unbound form.

    I hope someone can spare the time to help me sort this one out.

    I have created a form and query to allow deletions on selected records. For instance the query selects out only those records which have a TraniningStage = to “Proposed Training”. It should also only select those records which equal the text entered into the txtSSDetailsID and/or the txtCourse Code and/or the ProvCode (from the aStaffTrainingDeleteMainF)

    I have created an Unbound form called aStaffTrainingDeleteMainF
    I have also created a query called aStaffTrainDeleteProposedQ. The query has a number of fields including (where each criteria is entered on a different OR line and “proposed training” is entered next to each Like [Forms] statement):

    Field : SSDetailsID TrainingStage CourseCode ProvCode
    Criteria: Like [Forms]! "Proposed Training"
    [aStaffTraining
    DeleteMainF]!
    [txtSSDetailsID]

    Or : "Proposed Training"Like [Forms]!
    [aStaffTraining
    DeleteMainF]!
    [txtCourseCode]

    Or "Proposed Training" Like [Forms]!
    [aStaffTraining
    DeleteMainF]!
    [txtProvCode]


    Fields on the Query are:
    ID
    StaffID

    SSDetailsID criteria has
    Like [Forms]![aStaffTrainingDeleteMainF]![txtSSDetailsID]

    TrainingStage criteria has
    "Proposed Training"

    CourseCode critereia has
    Like [Forms]![aStaffTrainingDeleteMainF]![txtCourseCode]

    ProvCode
    Like [Forms]![aStaffTrainingDeleteMainF]![txtProvCode]

    (Plus other fields)

    If I run this query without opening the form, and enter the text in the txtfields when the ENTER PARAMETRE VALUE BOX appears, then it runs perfectly. Having the ‘Like’ before the criteria statement allows me to utilize the wild card * (asterix) to give all records.


    On the aStaffTrainingDeleteMainF form is three unbound combo boxes. I want to use Combo Boxes so that I can either select from a list or enter * (asterix) for all records. I am having trouble with all three unbound combo boxes but will discuss the first field only:

    Entering an * (asterix) for all records on the txtSSDetailsID does not work. I keep on getting the error message: “The value you entered isn’t valid for this field”. And for the other two unbound combo boxes the message is “ the text you entered isn’t an item in the list”.

    txtSSDetailsID is an unbound combo box but uses the following select query to help with the selection of the correct ID. It is written as:

    SELECT [aStaffSiteDetailsQ].[ID], [aStaffSiteDetailsQ].[StaffID], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[SiteCode], [aStaffSiteDetailsQ].[Site], [aStaffSiteDetailsQ].[RoleTitle], [aStaffSiteDetailsQ].[StaffActInact] FROM aStaffSiteDetailsQ ORDER BY [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[Site];

    This Select Statement works.

    It also has an On Click Event Procedure, which has the following code but it does not work as I keep on getting the error “Run time error 2046. The command or action ‘Refresh’ isn’t available now”:

    Private Sub SSDetailsID_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    Me!StaffID.Value = Me!StaffIDLU.Value
    End Sub




    The following are fields I am using on the form
    ***
    StaffID is an unbound text box

    *** The following are Dlookups BUT THEY ARE NOT WORKING.
    And changes to my selection are not showing up in the lookup fields because I cannot refresh the form.

    Staff1stNameLU
    =DLookUp("[Staff1stName]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")

    ***
    StaffLastNameLU
    =DLookUp("[StaffLastName]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")
    ***
    RoleTitleLU
    =DLookUp("[RoleTitle]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")
    ***
    StaffActInactLU
    =DLookUp("[StaffActInact]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")
    ***
    SiteCodeLU
    =DLookUp("[SiteCode]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")
    ***
    SiteLU
    =DLookUp("[Site]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")
    ***
    StaffIDLU
    =DLookUp("[StaffID]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID]")


    I hope I have made some sense here, if not please do not hesitate to contact me as I really need some help.

    Thanks Karen

  2. #2
    Join Date
    Mar 2009
    Posts
    5,286
    Karen,

    About comboboxes first: A combo has a property LimitToList that specifies wether you can enter a value in the Text part of the combo box that is different from the values present in the list part. If this property is set to True, any entered value that does not match a value in the list will be rejected and produce an error. You can trap this error and handle it with the OnNotInList event of the combo. For a detailed explanation search in Access help for LimitToList and OnNotInList.

    Moreover your combo txtSSDetailsID has many columns. By default a combo has a BoundColumn property that specifies the column from which a value will be returned when you address the combo using the Me.Combo syntax.

    If you want to retrieve values from other columns than the bound column, you have to use the full addressing syntax, like this (.Value is optional):
    Code:
    Me.Combo.Column(x).Value
    Where x specifies the column from which the value is to be fetched. Be aware that the first column (ie. the one more to the left) is Column(0) etc...
    There are interactions between the LimitToList and the BoundColumn properties, once more consult Access help about this.

    So I guess that to have your DLookUp functions working the way you want, the Criteria parameter should be something like:
    Code:
    =DLookUp("[Staff1stName]","aStaffSiteDetailsQ","[ID]=Forms![aStaffTrainingDeleteMainF]![txtSSDetailsID].Column(2).Value")
    By the way, if you use the DLookUp function in the module of the form where the combo is located, you can use a shorter sytax to address it, like this:
    Code:
    =DLookUp("[Staff1stName]","aStaffSiteDetailsQ","[ID]=Me.[txtSSDetailsID].Column(2).Value")
    Just another little piece of advice: Every time it's possible try to use the RunCommand method of the Application object instead of Docmd.DoMenuItem, for instance:
    Code:
    Application.RunCommand acCmdSaveRecord
    Up to know MS has provided a mean to maintain compatibility among versions of Access with the acMenuVerxx parameter but there is no garanty for the future, which means that if a feature is moved somewhere else (in another menu or at another place in the same menu) in a future version of Access, your code might stop working as it relies on the position of a command in a menu.

    Have a nice day!

  3. #3
    Join Date
    May 2002
    Posts
    157

    Still Confused

    Hi Sinndho

    Thanks again for trying to help me here, I really appreciate it.

    I have spent all night reading Access Help, the Access Manuals I have at home, searching the internet and rereading your email and trying different things out .... but to no avail.

    I cant seem to get anything your suggesting to work. I know it is something I am doing wrong but I just don't know what it is. I am so tired now that I think I will just go to bed.

    Can you help me on a step by step basis? I think I understand what you have said about LimitToList and OnNotInList.

    Remember I have an unbound form which has 3 unbound combo Boxes in it.

    The first Combo box (txtSSDetailsID ) has a Row Source which references:
    SELECT [aStaffSiteDetailsQ].[ID], [aStaffSiteDetailsQ].[StaffID], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[SiteCode], [aStaffSiteDetailsQ].[Site], [aStaffSiteDetailsQ].[RoleTitle], [aStaffSiteDetailsQ].[StaffActInact] FROM aStaffSiteDetailsQ ORDER BY [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[Site];

    The second and Third Combo boxes have Row Sources which reference Queries.

    I have changed the LimitToList Property of each combo box to NO.

    Entering an * (asterix) for all records on the txtSSDetailsID still does not work. I keep on getting the error message: “The value you entered isn’t valid for this field”. The message for the other two unbound combo boxes (“ the text you entered isn’t an item in the list”).

    From my reading and from what you have said I need to write some code or create a macro to allow my * (asterix) to be accepted. I just cant work out what this code or macro should be. I do not want the * to update any of my tables this is why I am keeping them as unbound boxes on an unbound form.

    The other point I have noticed is that the txtSSDetailsID is behaving as though it only wants to accept numerics this could be because it is bound to the [ID] column which is a numeric. It seems that it does not like getting the * which I think might be taken as an alpha.

    Can you help me with the Code?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,568
    if you have multiple values coming from the same table, then rather than doing a series of dlookups it would make more sense to use a recordset to retrieve all the values in one hit.

    dlookups are expensive in processor and netwrk time, they can be useful but they should be used sparingly
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,286
    Karen,

    Ok. As Access won't accept the asterisk because it's not in the list, we'll add the asterisk to the list. The RowSource property of the combo becomes:
    Code:
    SELECT [aStaffSiteDetailsQ].[ID], [aStaffSiteDetailsQ].[StaffID], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[SiteCode], [aStaffSiteDetailsQ].[Site], [aStaffSiteDetailsQ].[RoleTitle], [aStaffSiteDetailsQ].[StaffActInact] FROM aStaffSiteDetailsQ UNION  "*" AS Col0, "*" As Col1, "*" AS Col2, "*" AS Col3, "*" AS Col4, "*" AS Col5, "*" AS Col6, "*" AS Col7 FROM aStaffSiteDetailsQ 
    ORDER BY [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[Site];
    Like that the first line of the combo list will display an asterisk in every column and you can reset the LimitToList property of the combo to True as the asterisk now is an item in the list.

    You can then build your criteria (in the AfterUpdate event of the combo for instance) but as you use the Like operator you have to include its parameter in single quotes, like this:
    Code:
    Like '" & [Forms]![aStaffTrainingDeleteMainF]![txtSSDetailsID] & "'"
    I hope this helps.

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •