Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select value from record (Access 97 SR-2)

    I am opening a query using DoCmd.OpenQuery, after I get the query results sorted the way, I want to select the value of a field in the first record and pass it to another form all in the same code. Basically the user is opening a data entry form, but in the background, I want to automatically select the next available part number (primary key) and automatically fill in that number (foreign key) in a table that describes the details about the new label. After I go to the first record, how do I select that value and capture it to be used on the next form?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select value from record (Access 97 SR-2)

    Are you opening this query from the code behind the data entry form? What do you mean by the next available part number? What is it that makes it "available"? You mention a next form as well, so that makes a query, a table and two forms in 3 sentences.

    I think you're going to have to provide more detail before we can provide any useful assistance.
    Charlotte

  3. #3
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    Right you are Charlotte. Let me try again and be more clear. The database is to store information relating to identification labels that are made for various products. The primary key for the main table (Labels) is PartNumber. There are several part number series that are sequenced according to a manufacturing scheme. I have setup in the database so far a form that has a separate button for each different label sequence. Each button is to open a data entry form for that particular sequence (that's two forms open so far).

    That part all works fine. But since the next part number has to follow the manufacturing number scheme, I was trying to tell the user what the next part number was in that sequence, rather than have them pick one from a list, (lots of reasons why, the main thing is I want them to use the next available number). So I created a separate query to find that number. The query works by itself, but now to streamline the process, I am trying to combine everything into one action executed in code when the user initially clicks the button to select the appropriate entry form, it would also run the query (in the background) to select the next available part number.

    I wanted to pass that value from the PartNumber field in the first record returned by the query to the PartNumber field (foreign key) of the data entry form. Now I am tangled up in all of my forms and querys. To summarize, (1) selection form opened, (2) run query in response to onclick event to find the next part number, (3) grab the value of the PartNumber field of the first record returned by the query (here is my hole), (4) open the appropriat data entry form, (5) pass the next part number to the data entry form. Below is the SQL statement for the query and the code behind one of the selection buttons. Currently there are 16 separate buttons, I've been thinking after doing a lot of searching and reading last night that I should have a drop down list and one button for the user to select the appropriate form.

    Thanks for your reply, if you have suggestions to combine and simplify, I'm all ears.

    (qryNextLabel)
    SELECT Labels.PartNumber, Labels.Description, Labels.LabelTemplate, Labels.LabelSize, Labels.WhereUsed
    FROM Labels
    WHERE (((Labels.PartNumber) Like [Enter the 1st 6 digits of the part # required] & "*") AND ((Labels.LabelTemplate)="#") AND ((Labels.LabelSize)="#"))
    ORDER BY Labels.PartNumber;

    Private Sub butOpenNew215601_0_Click()
    On Error GoTo Err_butOpenNew215601_0_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stLabelType As String

    stLabelType = "215601"
    DoCmd.OpenQuery qryNextLabel, , acReadOnly
    DoCmd.GoToRecord , , acFirst

    stDocName = "frmNew215601-0"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit_butOpenNew215601_0_Click:
    Exit Sub

    Err_butOpenNew215601_0_Click:
    MsgBox Err.Description
    Resume Exit_butOpenNew215601_0_Click

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select value from record (Access 97 SR-2)

    I'm sorry, but I'm having trouble envisioning what you're trying to do even with the explanation. You don't usually try to pass values to fields in forms, you either pass an OpenArgs argument to the form when you open it (and then use code behind the receiving form to use the OpenArgs value) or you pass a WhereCondition string in the DoCmd.OpenForm expression. I don't understand what you mean by a separate button for each different sequence or how you would determine the definitive sequence for a part number series. How are you storing those part numbers in order to identify the "correct" sequence and determine which one is "next"?
    Charlotte

  5. #5
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    First off, thanks for taking the time to try and put your mind around this, I'm getting worried that I'm going about this all wrong. I'll try and explain better so you can point out where I'm wrong.

    The main table in the database is called Labels, the primary key is PartNumber. To answer one of your questions, I have pre-entered the data in this field because I don't want to rely on the user manually entering the next number correctly. (There will be a dozen engineers doing the choosing, that should tell you why). The PartNumber format is in this manner 2156xx-0yy, where xx can be a number from 00 to 25, and yy can be a letter except I or O. So I have a finite number of combinations, but still a lot. Each xx number defines a particular design label, each yy defines a label in that series.

    The next layer to the tables is a table for each xx series that defines explicitly the data on the label. These tables are named by their series, but only contain labels actually created, their PartNumber (which is the primary key for its table and the foreign key in the 1-to-1 relationship to the Labels table) is not pre-filled.

    I designed a query, that gives me all of the unused part numbers in a series, arranged such that the first one in the query is the next one I want the user to select. Originally, I had planned on them manually transferring this number to the blank entry form for that new label.

    There is a different entry form for each xx series, which I tied to an OpenForm button on a switchboard. The user selects the appropriate button (215600, 215601, 215602, etc) based on a template that I've given them (that little tidbit may help). I'm expecting the user knows which template they want to use, and will enter the data that they want to appear on the label in the fields for that particular series. The problem is, since the part number does not exist in the xx series definition table, the user has to enter that number returned from the above query, or my bright idea (doesn't seem so bright now) is that I would pass the first value returned from the query directly to the form, which I don't know how to do. I've been reading up on SQL statements in code (which I've never done), and am not up to speed on that yet. I've also been toying with the FindRecord function which I thought is what I needed.

    I don't know the OpenArgs argument, but I will go look at that next. Thanks again for looking at this, if I can't find a relatively easy solution, I think I will back off and have the user run the query, then manually enter the next part number in the data entry form. There is another thread on a different subject that seems to have at least some of the code that I will need to write, "Open form to the record that was viewed last". They are looking for the MAX value in their SQL statement, I'm just looking for the first value.

    I apologize for the two small novels (so far).

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    My 2 cents if I understand the problem.
    Open your query in design view.
    Select the menu View | SQL View.
    Just after SELECT enter TOP 1
    Save the query and run it. You will have only 1 record.
    In your form, change the textbox to a combobox and use the query as rowsource.
    As there will only be one record in the query, the user will have to use this entry.
    Francois

  7. #7
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    It works, it works, it works, no headache today thinking about this. Thank you Francois for pointing me in the right direction. It meant changing my whole switchboard form but now it is much simpler anyway. Rather than a separate button for each data entry form, I used an unbound text box (LabelTemplate) as the basis to open the correct form as below:

    Private Sub butOpenDataEntryForm_Click()
    On Error GoTo Err_butOpenDataEntryForm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stLabelTemplate As String

    stLabelTemplate = Me.LabelTemplate
    stDocName = "frmNew" & stLabelTemplate & "-0"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Then I modified my query to use the text box as well, and added your suggestion to the SQL statement, the final is here:

    SELECT TOP 1 Labels.PartNumber
    FROM Labels
    WHERE (((Labels.PartNumber) Like [Forms]![frmMainSwbd]![LabelTemplate] & "*") AND ((Labels.LabelTemplate)="#") AND ((Labels.LabelSize)="#"));

    And finally, modifying the text box on each data entry form as you suggested tied everything together. Thanks again, Francois and thanks Charlotte for prodding me til I gave enough info so others could understand the problem. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  8. #8
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    Of course, now that it works, I've opened one other hole that I anticipated but can't handle correctly. If my query returns no records because I have run out of available Part numbers, I want a simple pop up message to contact me. I tried the following in the GotFocus property of the new combo box, but it doesn't do anything if the query returns no records. Should it go somewhere else, or am I referring to the box incorrectly?

    Private Sub PartNumber_GotFocus()

    If Me.PartNumber = "" Then
    MsgBox "There are no new numbers available, Contact Shawn or Bev for help." _
    , , "No Labels Message"
    End If

    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select value from record (Access 97 SR-2)

    Fran

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    If PartNumber is the name of the combobox then use

    If Me.PartNumber.ListCount = 0 Then
    MsgBox "There are no new numbers available, Contact Shawn or Bev for help." _
    , , "No Labels Message"
    End If

    End Sub

    But you dont have to wait that the combo have the focus.
    Maybe you could do it in the On current event of the form.
    Or even in the on open event of form. In this event, if you add a line Cancel = True after the msgbox line it will cancel the opening of the form.
    Francois

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    I don't know if my code is better than that from Hans.
    Mine is shorter, but you have to put it in all the forms.
    Hans's code in only needed once.
    Francois

  12. #12
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    This looks exactly like what I want, I tried initially to include it with the form opening, but I wasn't sure how to refer to it. So I will move the code back to the form opening, then I only have to do it once rather for each form. I will try this later on today. Thanks for your reply.

  13. #13
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    Thanks Francois for fixing my code and I will back up so the form never opens as you suggested. Have a fine day.

  14. #14
    Lounger
    Join Date
    Sep 2001
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    I got a chance to try this, the code as I have it currently is below. I get an error that I've never seen before and it doesn't tell me where in the code the problem is, the error says (Too few parameters. Expected 1.)
    Any ideas?

    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stLabelTemplate As String
    Dim stSQL As String
    Dim rs As Recordset
    Dim fEmpty As Boolean

    stSQL = "SELECT TOP 1 PartNumber FROM Labels WHERE " & _
    "PartNumber Like '2156' & [Forms]![frmMainSwbd]![LabelTemplate] & '*' " & _
    "AND LabelTemplate='#' AND LabelSize='#'"

    Set rs = CurrentDb.OpenRecordset(stSQL)
    fEmpty = rs.EOF
    rs.Close
    Set rs = Nothing

    If fEmpty Then
    MsgBox "There are no new numbers available, Contact Shawn or Bev for help." _
    , , "No Labels Message"
    Exit Sub
    End If

    By the way, the string LabelTemplate now is a two digit user entry rather than typing all six since the first four are always the same. It works in the query by itself.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select value from record (Access 97 SR-2)

    You have an error in the stsql line
    <pre>stSQL = "SELECT TOP 1 PartNumber FROM Labels WHERE " & _
    "PartNumber Like '2156" & [Forms]![frmMainSwbd]![LabelTemplate] & "*' " & _
    "AND LabelTemplate='#' AND LabelSize='#'"</pre>

    After 2156 replace single by double quote aswell as before *' "
    Francois

Page 1 of 2 12 LastLast

Posting Permissions

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