PDA

View Full Version : Solved: My Kitchen needs a Loop



Lister
08-16-2004, 06:18 PM
Hey guys
I have been toying with the idea of a simple validation form for a input screen.
I have a list box with all the utensils in my kitchen draw. If I buy a new item and I want to add it to my inventory, first I want to check that I haven?t already got one. I type the item number in the Unbound field then click the [Is This In My Draw?].Button
The code would then check the item numbers the ListBox.Column(1), if there is a match it would open the form [What?s In My Draw] and tell me what I paid for it.

But if there is no match run the error message. Run the Yes/No error message box and ask me if I wished to add the ?new? item to the draw.

Yes = Blank [What?s In My Draw] form ready for input.
No = End Sub

Now when you have a look at my code I know that what I have for checking the ListBox0.Column(1) against the unbound Text.6 is rubbish.
And I know that there should be a Loop out there that can run through the items in the ListBox and compare it with the value in the Unbound field ([Is this in My Draw?])
But I have been unable with my limited knolage of loops to create one that will work error free.
And after a few hours of crawling through the search of list boxes and code I?m having a break and going to bed.
So I just banged it out because I know it will run with no errors stopping it, and with any luck will give you and idea of what I'm after.
I know that someone out there will think that this is a "doodle" and post the code that I am missing. (I hope)
Anyway have a look and see if you can help.

It?s in Access2K

Thanks all.

Lister
08-23-2004, 08:14 PM
Fixed the problem.


Private Sub cmdCheckList_Click()
On Error GoTo Err_cmdCheckList_Click
'The below loop will activate on click for button cmdFullCode
'First the user must add a new "item" number to the unbound Text4 field
'If there is no value an error message will run promting the user to enter a value
'Then the loop will check each item in the list boxs agains the value in Text4
'If there is an equal value the form "frmItem" will open showing full details for the item
'If there is not a match a yes/no message box will open asking the user if they want to add a new item.
'If yes a blank form "frmItem" will open.
'If no the sub will end.
Dim strItem As String
Dim lst As ListBox
Dim i As Integer
Dim j As Integer
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmInMyDraw" 'for the form criteria
strItem = Nz(Me.Text6, "Null") 'The item typed into Text4 will be seen as a string.
If strItem = "Null" Then 'This will look after the error it it's run with out a value in Text6
MsgBox "You must enter a value for the item value filed"
Exit Sub
End If
'==Now the Loop==
Set lst = Me.List2
j = Me.List2.ListCount - 1
For i = 0 To j
If lst.ItemData(i) = strItem Then 'If there is an equal value the form "frmItem" will open showing full details for the item
stLinkCriteria = "=" & Me![Text6]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
Else
'MsgBox "Next Loop", vbExclamation '==I removed this line, but you could have another comand here==
End If
Next i 'loop will now check the next itme in the list
'If there is not a match a yes/no message box will open asking the user if they want to add a new item.
If MsgBox("Item not in Draw. Do you want to add the item?", vbYesNo, "What Now") = vbYes Then
DoCmd.OpenForm stDocName, , , , acFormAdd, acNormal, 2
Else
Set lst = Nothing
End If
Exit_cmdCheckList_Click:
Exit Sub
Err_cmdCheckList_Click:
MsgBox Err.Description
Resume Exit_cmdCheckList_Click
End Sub

Just had to sort the Loop out. :*)

The next part that I am working on is if the the item [i]is a new item. And if the user clicks the "Yes" to the new item on the Yes/No pop up.
Can I add the item number that is currently in the unbound text field ([Text6]) directly into the Form.frmInMyDraw.[ItemKey] ?

Check the attachment and see if anyone has any ideas.

Thanks

___
08-24-2004, 05:19 AM
If MsgBox("Item not in Draw. Do you want to add the item?", vbYesNo, "What Now") = vbYes Then
DoCmd.OpenForm stDocName, , , , acFormAdd, acNormal, 2
Forms!frmInMyDraw!ItemKey = Forms!frmNewToTheDraw!Text6
Else

and change AutoNumber in your table to Number. Then add this to the close button of your form frmInMyDraw....

DoCmd.Close
Forms!frmNewToTheDraw!List2.Requery

HTH
:)

Lister
08-25-2004, 02:47 AM
Awsome :)

I was using the autonumber just for the example. It would most likly be a production or contract number.
Just new someone would have the code lying around.

Thanks ______

___
08-25-2004, 02:49 AM
No probs, glad you got it to work :)