Results 1 to 10 of 10

Thread: WOW (2002)

  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    WOW (2002)

    Hi all

    I need some help regarding a way of working please. Let me explain what it is I'm trying to do:

    I have a form which displays the details of my products table, I have a separate text box on the form which shows the current balance of each stock item. So when I scroll through each product the text box keeps changing to show the current available balance. (this was all done with the aid of the wizards linking to a separate query so all is well at this point)

    What I have done, is created a command button next to this text box which I would like to call "Details" when the button is pressed I would like access to go and "pull out" all the transactions, into and out of stock, which make up the balance shown.

    What I have thought so far is the product code that is currently displayed should go into, and be held in a variable, I then need to open and display a query which extracts the movement in and out for this particular product code.

    Now I have started my code as follows:

    Private Sub cmdDetails_Click()

    Dim str1 As String
    Dim rs As Recordset
    Dim dbs As Database

    str1 = ProductCode.Value

    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("qryTransactionSummary1", dbOpenForwardOnly)

    End Sub

    To be honest this is where my knowledge begins and ends and I have no idea how I can do this.

    Can anybody help?

    I trust my explanation is sufficient

    Many thanks
    Hayden

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: WOW (2002)

    I would approach it by planning on displaying a continuous form of all the transactions. Design the form so it looks like the Datasheet view of a table or query. On your original form, your command button would then use the Docmd.OpenForm method to launch this form. In that command line, you would also specify a WHERE clause. Let's assume you already have a textbox on your mainform that holds the Product#, and let's assume the textbox is [txtProductNo]. Your code would be something like this:
    DoCmd.OpenForm "frmProductDetails",wherecondition:="ProductNo =" & txtProductNo
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    Thanks for the help Mark.

    I think I understand what your are telling me, but my execution isn't right.

    Here is my revised code:

    Private Sub cmdDetails_Click()

    DoCmd.OpenForm "sfmTransactionSummary1", wherecondition:="ProductID =" & Combo42

    End Sub

    When I run this I keep getting a "Enter Parameter Value" input box

    Where am I going wrong?

    Kind regards
    Hayden

  5. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,326
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: WOW (2002)

    What parameter cause this message? I used ProductID in my example, but is that the field in your table?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    I'm not too sure, when I click on the button I get the "enter parameter value" message, but also the first three characters of the product code (ProductID in the base table) itself is displayed in the message box above the input field space.

    When I press 'OK' , the form I want opens, but in single form view and not datasheet mode.

    But rather than waste your time Mark, let me go away and try and find out exactly where it is going wrong and then come back, (i've probably not followed the instructions correctly) if that's ok

    Kind regards
    Hayden

  7. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    Hayden...

    What kind of data is in the ProductID field?... If the data is text, you probably need to add apostrophes into the statement...
    Try... DoCmd.OpenForm "sfmTransactionSummary1", wherecondition:="ProductID ='" & Combo42 & "'"

    HTH

  8. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    Hello Trudi I have checked the data type and it is a text field, and having tried the code you proposed it has worked, thank you very much for your time.

    One further question though, when the form opens it is still opening in single form view, even though I have set the forms properties to datasheet. I have checked the help file for the 'Openform' command but I cannot see where I can set the view properties in the code, (if in fact it is set here)

    Please excuse my ignorance but I am still trying to learn and acquire the skills of database development and VBA code, so I would very grateful if you could assist in this matter.

    Kind regards
    Hayden

  9. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    Good Morning Hayden...

    Don't worry about it... It's not a problem... I was in your place a few years ago... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Try, DoCmd.OpenForm "sfmTransactionSummary1", acFormDS, wherecondition:="ProductID ='" & Combo42 & "'"
    Now this is just a guess... I can't find a description of acFormDS, but this is the view argument in the statement and there are four choices (intrinsic constants):
    acDesign, acFormDS, acNormal (default), acPreview
    Something tells me that the DS stands for Datasheet but I could be wrong...

    Let me know how it goes...

  10. #9
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    It works!! Fantastic!

    Thanks very much!

    ... you mean this state of utter confusion passes after time?

    Kind regards
    Hayden

  11. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WOW (2002)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Somewhat... Just hang in there!
    Have a great 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
  •