WOW (2002)

[HAYDENNOTHNAGEL]

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

[MarkLiquorman]

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

[HAYDENNOTHNAGEL]

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

[MarkLiquorman]

What parameter cause this message? I used ProductID in my example, but is that the field in your table?

[HAYDENNOTHNAGEL]

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

[Alexya1]

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

[HAYDENNOTHNAGEL]

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

[Alexya1]

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...

[HAYDENNOTHNAGEL]

It works!! Fantastic!

Thanks very much!

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

Kind regards
Hayden

[Alexya1]

<img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Somewhat... Just hang in there!
Have a great day!