Simple VBA program

Gener

Registered User.
Local time
Today, 15:28
Joined
Jul 27, 2012
Messages
14
Hello, I may be requesting a lot, but I have a problem. I have made a program that can identify a entry in a field (ID) and change the quantity of a field. However, if can only identify numbers. I have the code as follows:
Code:
Option Compare Database

Sub AddToInventory()
Dim tb As DAO.Recordset
Dim I As Integer
I = 1
Do Until I = 2
On Error GoTo Canceled
Set tb = CurrentDb.OpenRecordset("Select InitialLevel from Inventory where ID2 = " & InputBox("Enter Item ID"))
If tb.EOF = False Then	
tb.Edit
tb!InitialLevel = tb!InitialLevel + 1
tb.Update

End If
tb.Close
Loop
Canceled:
End Sub

The "where ID2 = " only works with numbers. Is there anyway I can make this work with Strings? Basically, Find the string in a column and update the quantity. Thanks a lot, appreciate all the help.
 
Try

Code:
Set tb = CurrentDb.OpenRecordset("Select InitialLevel from Inventory where ID2 = " & Chr(34) & InputBox("Enter Item ID") & Chr(34))
 
Thanks, this worked perfectly. However, I had a couple more questions if you don't mind. Whenever I try to close the box now, it always keeps popping up, AKA infinite loop. Any idea how this might work?

Also, is there anyway to link this sub onto a command button in a form? Whenever I do it, it always gives me an error about the Automation object error or something, thanks.
 
You never increment the "I" variable, so you will get an infinite loop. Why is it a loop to begin with?

I don't see anything in the code that would prevent it from being behind a button. How exactly are you using it?
 
So basically I want a loop for the program, but is there any way I can make the program stop once I press cancel or X out of the program? The I variable was the only way I could implement an infinite loop.


As for the button, I tried a lot of things. I made a macro that used RunCode and put AddToInventory inside the RunCode. Unfortunately, I got an error after this, any idea why? Thanks again for all your help.
 
I have to run out, but set up something for the user to enter to exit the loop and test for it. Like

InputBox("Enter Item ID or 777 to exit")

and test for that value.
 
The loop serves no purpose that I can see. What is your intention?
 
I got the loop running, thanks. However, I still can't figure out why I can't run this code with a button? I am using Access 2010 if that helps. Now I am getting "The Function you entered can't be used in this expression."
 
Here is a solution for the command button:


Code:
private InvDone as Boolean

Private sub cmdAddToInventory ()

   if not InvDone then
      AddToInventory
   end if

   InvDone = true

end sub

Don't forget to set InvDone to false in the oncurrent event of the form.

Why this InvDone? To prevent multiple clicks on the button...

JLCantara.
 
Thanks for the reply, but how would I put this into a button? I tried the OnClick event in the button with =AddToInventory(), made a macro that does RunCode AddToInventory, but no luck.
 
Sorry, I forgot a minor detail:o:

Private sub cmdAddToInventory_Click ()

This is the on click event of your command button.

Note: =AddToInventory() forces Access to look for the function AddToInventory not the sub...
Don't forget to reset InvDone...

JLCantara.
 
Thanks for the quick replies- So what am I putting EXACTLY in the OnClick Event? Am I putting cmdAddToInventory_Click() ? Am I putting it with an equals sign or am I naming my button cmdAddToInventory? Thanks for the help.
 
1 - Name your button at your convenience - I used cmdAddToInventory as an example.

2 - The body of the event sub should be as in my post.

3 - F = Something() : means Something is a function.

4 - Something : means Something is a sub as in my post.

5 - You have coded a sub so it should be called accordingly.

JLCantara.
 
Ok, so I made a function that runs the Sub. I then put that function in a macro with RunCode. It obviously recognizes the function, but I get this error: "The function you entered can't be used in this expression". Any advice? Thanks again.
 
Holly fart! why do you refuse to do what I posted????:mad:

You don't need a function, you don't need a macro.

If you want to do it your way, well good luck:banghead:!

For me this is the end: no more e-mail notification.

JLCantara.
 
Sorry, I just got what you meant, haha. So I put AddToInventory in the OnClick event, but it gave me this error: Goods cannot find the object 'cmdAddToInventory'. Anyone else have any advice? Thanks for all the help.
 

Users who are viewing this thread

Back
Top Bottom