Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2006
    Posts
    8

    Unanswered: stLinkCriteria Problem

    I'm trying to get the contents of an unbound combo box on one form, when a button is clicked to open a different form and put the contents of the other one the new forms bound combo box. This isn't happening.

    That being said... The first form is a Search form. The combo box is an unbound combo box which is looking up records in a "persons" table. It is using the PK of the "persons" table.

    The other form is a "Log units in service" form. It is setup for data entry only. The combo box is however linked to a "shifts" table with the lookup information coming from the "persons" table.

    Both combo boxes are setup to hide the pk column by width and show the "LName, FName MI - PhoneNum".

    When I setup the onclick event of the search form, this is the code I used:

    Private Sub cmdSignPersonOut_Click()

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSignPersonOut"

    If IsNull(Me.cbNamePhoneNum) Then
    DoCmd.Beep

    Else
    stLinkCriteria = "[PersonFK]=" & "'" & Me![cbNamePhoneNum] & "'"
    DoCmd.Close acForm, "frmPersonSearch"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    EndIf

    End Sub


    THE PROBLEM
    The problem lies when I click the button, it takes me to the right form, however nothing is put in the combo box. The Field name "PERSONFK" is correct for the form, as well as the "CBNAMEPHONENUM" for the other form.

    I've also put a MsgBox at the end of the DoCmd.OpenForm to see what value it's bringing over. It's bringing over the cooresponding PK number that both combo boxes are using.

    Curious what I'm doing wrong. Many thanks to anyone who can assist.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    just a quickie, if you'll pardon the expression
    is PersonFK a text column?, normally I'd expect a FK to a person to be a number, pointing to a system generated (autonumber) value in the person table. if so you don't need the quote marks

    if it is numeric you are then trying to match a text value to a number, and it may well collapse. it may look the same, but most certainly isn't

    as a general rule i'd also consider doing an explicit converson of the data type especially when pulling values from a control (although VBA does a farily good job of doing datatype conversions & coercing)

    stLinkCriteria = "[PersonFK]=" & clng(Me![cbNamePhoneNum])
    what the telpehone number is you unique key!, you gotta be kidding, you cannot trust a telephone number to be a unique value to a specific person. some people share phones, some people don't have phone, some people quote phone numbers with or without punctuation / spacing. selecting that as a primary key is a mid to long term maintenatce nightmare (they can change their phone number meaning you have to ripppple those chages through the db)

    HTH
    Last edited by healdem; 06-22-06 at 06:33.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    Quote Originally Posted by wtf069

    Private Sub cmdSignPersonOut_Click()

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmSignPersonOut"

    If IsNull(Me.cbNamePhoneNum) Then
    DoCmd.Beep

    Else
    stLinkCriteria = "[PersonFK]=" & "'" & Me![cbNamePhoneNum] & "'"
    DoCmd.Close acForm, "frmPersonSearch"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    EndIf

    End Sub
    Try this :

    stLinkCriteria = "[PersonFK]=" & chr(34) & Me![cbNamePhoneNum] & Chr(34)

    or

    x = "[PersonFK]=" & Str(Me![cbNamePhoneNum])

    depending on string or number value.

  4. #4
    Join Date
    Jun 2006
    Posts
    8
    I've attempted all 3 suggestions without success.

    A little more background/info:

    All records in the tables use a standard autonum PK field. It isn't used to store useful info, just be the PK for the record. If I link back to it by FK as in the [PersonFK], I use a number field (as they are both the same type). The name of the combo box [cbNamePhoneNum] just shows what the information is. The actual bound column for the cb is the PK field.

    On the search form -
    cbNamePhoneNum
    Control source is blank
    Row source is 2 columns. 1 the bound one is PersonPK and the other is just a statement combining the Lname, Fname and phone number from there.
    Again, the bound column is the PK field.

    On the log person on form -
    PersonFK
    Control source is PersonFK from the shifts table
    Row source is 2 columns again, same as above. The bound one is PersonPK and the other is just a statement combining the Lname, Fname and phone number from there.
    Again, the bound column is the PK field.

    I appologize, but I'm not acustom to using forums such as this. Hopefully I gave you the information to assist.

    Thanks to healdem and emiel for their responses.
    Last edited by wtf069; 06-22-06 at 18:45.

  5. #5
    Join Date
    Apr 2006
    Posts
    157
    curiously, you're passing a criteria to open the Log... form in data entry?

    i think it is the combobox of the log... form that you should pass a criteria to
    Only quitters quit!

  6. #6
    Join Date
    Jun 2006
    Posts
    8
    Quote Originally Posted by syntaxerror
    curiously, you're passing a criteria to open the Log... form in data entry?

    i think it is the combobox of the log... form that you should pass a criteria to
    [PersonFK] is the name of the combo box on the "Log units on" form. So, from what you're saying, I should specify the [PersonFK] is "stLinkCriteria" after the form is opened...?

    So, it would appear that I'm attempting to open an exisisting record on a data entry only form.

    That being said, how would I just update the combo box of the "Log On" form with the stLinkCriteria after it is open?

  7. #7
    Join Date
    Apr 2006
    Posts
    157
    Although not quite the norm,

    I'm accustomed to setting up global variables for these tasks, I would naturally place an if then or select case on the form's load event to synchronize its combo box with the calling form through the global variable,

    which you may update at the command button that calls the 'log units..' form
    Only quitters quit!

  8. #8
    Join Date
    Jun 2006
    Posts
    8
    Quote Originally Posted by syntaxerror
    Although not quite the norm,

    I'm accustomed to setting up global variables for these tasks, I would naturally place an if then or select case on the form's load event to synchronize its combo box with the calling form through the global variable,

    which you may update at the command button that calls the 'log units..' form
    I think I get the jist of this. DIM the contents of the 1st combo box as a global varrible, and when the other form opens, choose to or not to put the contents of the global varriable in the 2nd forms combo box?

    If I have this right, please let me know.

  9. #9
    Join Date
    Apr 2006
    Posts
    157
    yes,

    but forwarned is forearmed, like I said, this is just quick fix solution,

    it might be cleaner to set the data entry form's combo box filter to the value set in the calling form.
    Only quitters quit!

  10. #10
    Join Date
    Jun 2006
    Posts
    8
    Quote Originally Posted by syntaxerror
    yes,

    but forwarned is forearmed, like I said, this is just quick fix solution,

    it might be cleaner to set the data entry form's combo box filter to the value set in the calling form.
    Hey, thanks for the info. I'll give it a try when I get some time. Thanks for the assistance.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the far smarter solution is to extract the value form the other form when required. then there is no problem of concurrency, or what value was set at what time.


    <mycontrol=forms!<mysourceformname>!<mycombo>
    OR if you want to push the value
    !forms<mydestiantionformname>!<mycontrolname>=<myc ombo>

    the one problme area that you may have is ensurign that the other form is available, so you might want to do this in a functuion and test that the 'other' form is open, or use some error trapping routine which gives and adequate message or default value.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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