New to VBA and Access, Need General Help :)

[Hek]

Hello all,

I am new to Access and VBA, know only the bare basics of access like how to create Tables, Queries, Forms and Reports, and how to sift through the data in each to a basic level, and some little things in VBA like the expressions such as =, <>, <, >, >=, <=. I have been hired by a company that needs me to figure out the system that the previous guy has made and fix some issues with it/make it run properly. They knew i had no prior experience and were happy to let me work it out on my own. I'm here to ask for some basic help with coding and some other general stuff if need be. The program the last guy who worked here built is for logging inventory, keeping the production information updated and is also used to place new orders. For example, they have a form with an ID for a kit (they sell car parts here.) So they have Kit ID, Kit Number, Description, Stock, On Order, Stock Build, Sold, Category and Notes. When someone inputs a stock value into the system, the incorrect value is automatically input into the "on order" and i have close to 0 idea why. If someone could reply to this thread (or better yet, contact me via email) that would be greatly appreciated. Attached is a visual representation highlighting the issue.

TLDR; entirely new to Access and VBA and need help with understanding basic VBA and also help with understanding this companies data base. Attached is a photo of the issue.

 

[theDBguy]

Hi. The best way to help you would be if you could post a sample copy of your db. Just a suggestion.

 

[The_Doc_Man]

The biggest problem dealing with inventories is that there are two ways to do them - the right way and the way that inexperienced designers most often try to use. Without knowing which kind of inventory method was used, we will have no hint of how to help.

However, may I suggest some reading that might help you sort out the mess? Using this forum's SEARCH feature (3rd from right in the menu ribbon near the top of the page), you can look up Inventory and see probably hundreds of articles.

Now, to clarify:

The way that most inexperience people try to use is to have a total number of parts in a field in a table. Then they struggle like heck to keep that field updated for each part. This is wrong. Something called "destructive interference" can cause this method to lose track of changes. The best definition of destructive interference is, in a shared application, "the left hand doesn't know OR CARE what the right hand is doing."

The right way is to have transactions that add or subtract parts. Then do a running total on the transactions. This way, every total starts at zero. Then you get shipments and you add a number of parts. Then you sell stuff and subtract some parts. There are ways to archive entries but that becomes an advanced topic and I don't want to confuse you too much right now since you claim to be new to Access.

 

[Hek]

How could i upload a sample version of the database without giving away any of the key data that may or may not be confidential to the business? there are a lot of previous data entries on here. Also, this account belongs to the previous employee who worked here, any questions asked before this date was someone else entirely with a different skill set to mine.

Edit: Thanks for such a quick response, is very handy as i am at work right now and am stuck on what i could do to move forward with this issue, Thanks.

 

[Hek]

Now, to clarify:

The way that most inexperience people try to use is to have a total number of parts in a field in a table. Then they struggle like heck to keep that field updated for each part. This is wrong. Something called "destructive interference" can cause this method to lose track of changes. The best definition of destructive interference is, in a shared application, "the left hand doesn't know OR CARE what the right hand is doing."

The right way is to have transactions that add or subtract parts. Then do a running total on the transactions. This way, every total starts at zero. Then you get shipments and you add a number of parts. Then you sell stuff and subtract some parts. There are ways to archive entries but that becomes an advanced topic and I don't want to confuse you too much right now since you claim to be new to Access.

This mostly makes sense to me, but how i would execute any of these tasks is beyond me, ive only been tinkering with/reading about access and VBA for around 4 days now, all ive managed to pick up is the general workings of tables and forms etc, and ive made a button present a window with some text (very very basic VBA i know), i wouldn't begin to know where to start on this big DB that ive got 0 idea about and know nothing about the code etc.

I highly appreciate your time and effort

Regards, Rhys.

 

[theDBguy]

How could i upload a sample version of the database without giving away any of the key data that may or may not be confidential to the business? there are a lot of previous data entries on here. Also, this account belongs to the previous employee who worked here, any questions asked before this date was someone else entirely with a different skill set to mine.

Edit: Thanks for such a quick response, is very handy as i am at work right now and am stuck on what i could do to move forward with this issue, Thanks.

Hi. All we need to see are your table structure and database design. We don’t need to see real data, but it would help a lot if you could put in some dummy data, so we can also see how the database works.

 

[Hek]

i have been informed that the last person ripped a template for this database from the internet and just modified it to be specific to this company.

Would it be easier to start from scratch and download a new one with all working functions and just tweak it to the companies specifications or should i attempt to fix the old one? im quite stuck as all of this is very new to me and i wouldnt even know how to transfer the old data into a new template, any input is welcomed and highly appreciated.

Regards, Rhys.

 

[Hek]

Current State of the Inventory System

Stock- Kits that are available are ready to be shipped
On Order – number of kits which are being made specifically for a customer
Stock Build- Number of kits which are being built specifically for stock
Sold- Number of Kits sold
*Additional*
Temp Number
Stock Temp
There are two hidden columns (Temp Number and Stock Temp) which help with the calculations that assign values to the previous 4 columns. These values should always view as “0”.
To access the raw data of the Inventory system, all systems must be exited. Then open “Company Name V16 Data” access database. This is located in Z drive – Access, if there is two of the same database that means that the system is still open somewhere and you will have to find it and get out of it, other wise you wont be able to change anything in it.

The inventory system is in a state that when an order is created, all inventory is automated from the start, meaning that it will automatically record the appropriate value, the only issue at this point is if an order is deleted the appropriate values are not automated, meaning that it may have to be input manually if an order is deleted. To change the values of a kits.

This is some info the last employee had written about how it runs, if anyone understands this and would like to help, please get back to me.

Regards, Rhys.

 

[isladogs]

That's better than the explanations many programmers leave behind

The first part should be self explanatory.
The raw data part means the database is split and data stored in a backend on the Z drive. End users should have no reason to look at it.

The final paragraph is less clear. I think its saying that everything is automated unless an order is deleted in which case some corrections need to be done manually

 

[Hek]

The final paragraph is less clear. I think its saying that everything is automated unless an order is deleted in which case some corrections need to be done manually

i also thought that is what was being explained, probably means what we think it means then. the values in the "on order" section is in the minuses which doesn't make any sense, anything i can do in either VBA or just in access to rectify this issue?
:banghead:

 

[Hek]

here is a snapshot of what the table basically looks like, as you can see, the "on order" values are off, not sure how to rectify this issue.

 

[Hek]

There are two hidden columns (Temp Number and Stock Temp) which help with the calculations that assign values to the previous 4 columns. These values should always view as “0”.

Edit: i have made sure that the two hidden columns (Temp Number and Stock Temp) values are sitting at 0 like they should.

 

[isladogs]

If you know what the values should be you can correct any that are 'off' in the underlying table..but make sure nobody else is using it at the same time.

To prevent future errors will need code changes. Without knowing what code you have now, its difficult to advise further.

 

[Hek]

If you know what the values should be you can correct any that are 'off' in the underlying table..but make sure nobody else is using it at the same time.

To prevent future errors will need code changes. Without knowing what code you have now, its difficult to advise further.

They have told me that they want to make it run automatically so they can just click a button and it all happens without any manual input.

how would i access the previous code that has been written specifically for this function? if you can help me pull the code up, i can post the code here.

Regards, Rhys.

 

[Hek]

im about to leave work, will reply here either when i get home or when i get into work tomorrow.

Thanks to everyone so far who has tried to help, its a life saver

Regards, Rhys.

 

[isladogs]

If the code uses VBA, you can open the form in design view then click View Code on the Design ribbon. Find the relevant section of code and post it here using code tags (# button on toolbar).
However you will probably find that more than one section of code is involved.

There are two other possibilities though.
It could involve macros or embedded macros. All you can do in such cases is post a screenshot but as few of us use these, help may be harder to get.

No rush to post this. Do so when convenient to you. Hopefully someone can help.

 

[Hek]

so i put the inventory modification in design view, then clicked the "view code" button on the design ribbon, and have been presented with no code, here is a screen cap of what i can see.

any other suggestions as to where the relevant code could be hiding?

 

[Hek]

both the inventory form and the inventory modification form have no code associated with them, any idea as to why this is? Every other form ive looked at has code to make it run, no idea why there is no code associated with the inventory/inventory modification forms.

Any help is extremely appreciated.

 

[Uncle Gizmo]

both the inventory form and the inventory modification form have no code associated with them, any idea as to why this is?

Macros? ... .... ..... .... .... ..... ...... ...

 

[Hek]

Macros?

How would i view these? ill have a look now.