Unanswered: would appreciate help creating simple form
hi - i've read up on this quite a bit and haven't been able to figure out what i need, and i doubt i'll need this kind of knowledge again so formal schoolings seems impractical - i'm hoping one of you folks can help me by providing either a sample .mdb file or brief, simple instructions. i think this is something that's very simple for someone with extant know-how.
what i'm trying to accomplish:
a form with the following fields:
> part number
i'll detail each breifly:
part number: this will start with a list i've been supplied. user should be able to search by scrolling and/or "find-as-you-type" functionality. user should be able to input new part number here, if not already present in the database.
quantity in/out: this is relative to the part number. a basic +/- number value that affects the next field.
balance: maintains a running total of the adjustments made via "quantity in/out" from day of install through present.
in application: user open the database, first field he encounters is part number, he searches that until he finds the part number he's looking for (or if it doesn't exit already, types it in and hits 'enter' to create a new record, which will henceforth be part of the list he just searched). after locating (or entering) the part number, he enters a plus or minus value to the next field, "quantity in/out". this immediately adjust the last field, "balance" which is dislayed for reference but not editable by the user.
the thing that's given me the most difficulty is getting the "quantity in/out" field to reset - i can get it to take a number value, but after hitting 'enter' i need the value to disappear, so that another entry can be made later.
It would appear that you want to be able to enter a minus number for parts ordered and a plus number for parts recived from your suppliers and then for the balance to reflect this. Is that correct.
You could placed an unbound text box on your form when someone enters the amount. Lets call this unbound text box InOut
You put a second unbound text box (which when have made this up you would hide from view) and we will call that CalBal.
And I will assume the field for the balance is called Balanced.
In the unbound text box CalBal you enter for its data source
So if your balance was 10 units and you entered -6 in InOut box then CalBal would show 4.
You now make a little macro with a couple of SetValue actions.
The first SetValue action will be to set the value of the field Balance to be equal to the result in CalBal.
The second SetValue action will be to set the value of InOut as Null.
You then attach this macro to the LostFocus event property on InOut. That mean when you hit Enter the focus will be lost from InOut and the macro will run. Oh, you should also have the first action line of the macro as SaveRecord. On earlier versions of Access that is done via the macro action DoMenuItem.
You could also have two labels (or command buttons) to run the macro from and one label would be labeled Orders (meaning outgoing) and the other Supplied for incoming from your supplier. You would attach the above macro to the OnClick event for either the labels or command button and make one change to the macro.
In fact you would make to macros and the one attached to the Orders label would SetValue the InOut box to [InOut]*-1
That way the user could just enter a number like 10 and if clicks Orders then the 10 will be changed to -10. In other words the user does not have to enter -10 or 10 because the either label or button takes care of that.
mike: thanks for the reply. unfortunately, i didn't understand a thing you said. i guess i should have prefaced with "i'm an idiot". i'll give gareth's pre-gen a shot and if that fails, i'll try to more clearly elucidate my utter lack of anything even remotely resembling a clue in regard to access, databases and VB script.
gareth: alrighty i appreciate your offer, and will send an email presently.