Please Help Bulid VBA Code/Application

[astra84]

Hi All,

Im new to the forum.

I am trying to build a small formula/code in Access using VB. I have done a little VB but a while ago.

Basically i would like some one to Build/Help me build and Access based application.

I know this should be easy for someone with experience.

The idea behind the apllication is as follows:

I work a warehouseing company where we have a lot of racking with is managed by a warehouse management system and the idea is to use this application to setup the rack layout.

This is what i am hoping is possible.

When opening access you will be greeted with a form With 2 buttons, Beam Setup and Location setup.

When you click on Location setup another window will open ansking for the start location

Example - Rack 211 X coordinates 001 Y Coordinates 01

You then be asked Stop Location

Example - Rack 211 X coordinates 124 Y Coordinates 01

Then Continue

Once continue is pressed the Code will automatically fill in the locations inbetween the start and stop locations, I then want to be able to export this to a txt file which i can then import into the warehouse management system.

The beam setup will do the same but instead of every location it will do every 3rd location, So 001 004 007 010 etc.

The other thing i would like this todo is multiple levels ie up to Y coordinates 06.

I Know there is alot above and i can forword an example of the txt file layout incase it is needed.

The last thing is i might need to edit this in time so could you please not password the coding, I have no intention of using this for anything other than i have described above.

Thanks in advance.

Please contact me if any further info is need.

Sorry i forgot to mention, I would attemp this myself but i dont know where to start, So if anyone could assist me in doing it my self i am happy to give it a try

Best regards Chris

[CreganTur]

When you click on Location setup another window will open ansking for the start location

Example - Rack 211 X coordinates 001 Y Coordinates 01

You then be asked Stop Location

Example - Rack 211 X coordinates 124 Y Coordinates 01

Then Continue

Once continue is pressed the Code will automatically fill in the locations inbetween the start and stop locations

What exactly do you mean by this? Do you want it to show all of the possible X-axis locations from start to stop? i.e.:

Rack 211 X coordinates 001 Y Coordinates 01
Rack 211 X coordinates 002 Y Coordinates 01
Rack 211 X coordinates 003 Y Coordinates 01
Rack 211 X coordinates 004 Y Coordinates 01
...
Rack 211 X coordinates 122 Y Coordinates 01
Rack 211 X coordinates 123 Y Coordinates 01
Rack 211 X coordinates 124 Y Coordinates 01

[astra84]

Hi,

Yeah thats it mate, And then then option to go up to the next Y coordinate 2,3 and so on.

Then with the Beam setup every 3 X coodinate locations 1,3,7,10 etc.

Thanks Chris

[CreganTur]

Could you attach a copy of the txt file you want these results output to?

Just click Go Advanced and then Manage Attachments to add it to your post.

[astra84]

Hi,

The file has been uploaded (i Hope)

The first page is the location set up and the second is beam setup. I have had to put it in word to upload.

A couple of revisions on what is needed:


1. The MHA (in Both setups) is the same all the all though but needs to be an option at the start, as there are various ones to use within the warehouse.
2. On location setup the last colume - SECTION is just the beam, If possible this needs to be completed automatically. So location 1,2,3 are on beam 1, 4,5,6 are on beam 2 etc.
3. The TYPE and MAXWGHT on beam setup need to be like point 1, the same all the all though but needs to be an option at the start, as there are various ones to use within the warehouse.i Hope this helps you out,

And thanks again for helping me

Regards chris

[CreganTur]

I just had three projects assigned to me, so I'm very busy right now. So far all I've done is some basic form design, as seen in the attached database. I haven't done any of the math functions or coding.

Here's an idea you can try working on, and I will try to continue to help you, I just don't know when I'll get more free time.

Add a button to the Beam and Location forms. User enters the start and stop information. When the click the button a new, temporary table is created. Math functions run to increment all of your values (ex: move X-coor 001 to 002)- and every time it does this the new value is written into the temporary table until the stop position is reached.

All of the values in this temporary table would be shown in a subform (in datasheet view) so the user can review it. Then there would be another button that would write the completed data to a text file.

I don't know ho wmuch you know about VBA or Access, but I wish you luck!

[astra84]

Thats really good, Thanks. Im not sure i will be able to do them bits. I think thats a bit to advance for me.

[CreganTur]

I had a small breakthrough in getting the X-coor results to increment correctly, and writing everything to a text file (Still have to work out some formatting issues though...:whip )

Biggest hurdle I've come across is the Section. Still not sure how to handle it.

Do you have an example of a text file that shows the results if the user enters a Start Y-coor of 01 and a Stop Y-coor of any number greater than 01? I just want to see how the text file should be formatted if the Y-coor needs to be incremented.

Let me know if this doesn't make sense.

[astra84]

The only txt file i have are the ones that i attached the other day. Im not sure on what to sudjest for that one. Its not a major part in the application so i wouldnt worry about it to much

Thanks

[CreganTur]

NinjaEdit: Talked to a programmer friend of mine a little while ago and we worked out a solution.

I kept looking for some fancy solution and forgot about the basics:doh:

Here's what I used:

Section = CInt(XStart + 1) / 3

This gives me the correct section number, that meets the OP's needs. I forgot that when you divide integers that it doesn't take any of the decimal info into account.

________________________________
Original Post:

I could use some help from a more experienced Access coder.

Please take a look at the attached file- it's the database I've built so far. I've got it working to increment the X-coor and Y-coor as per OP's specs, but I cannot figure out how to increment the Section numbers as per his specs.

The only way I can think of to do this is to use Select Case, but that would be a lot of cases! As long as there is a set max for X-coor, then this is a feasible, if work intensive option:
Select Case
Case XStart = 1 Or XStart = 2 Or XStart = 3
SectionStart = 1
Case XStart = 4 Or XStart = 5 Or XStart = 6
SectionStart = 2
Ad Infinitum

However, I'd much rather make Access do the math for me. But, if it's not possible, then I'll just code the cases:stars:

Anyone have any ideas?

NinjaEdit: Look at frmLocation in the database- that's the one I'm currently working on, and the one this thread is about.

[CreganTur]

I'm back to say that my above 'solution' doesn't work.

I'm having a really hard time coming up with the math to make these calculations work, and I'm really hoping that someone out there can help me make this work.

Here's what I'm trying to accomplish(I'm currently working with integers as all values must be whole numbers):

If X-coor is 1 Or 2 Or 3 Then
Section = 1
If X-coor is 4 Or 5 Or 6 Then
Section = 4
If X-coor is 7 Or 8 Or 9 Then
Section = 7
...ad infinitum

What I'm hoping to achieve is a math function that will give me the correct Section value, no matter what value I put in for X-coor.

[CreganTur]

Astra,

I've attached a working draft of a database that does what you want. It should meet your specs.

On opening the database there is a main window where there is are Beam and Location buttons. Clicking on one of these will bring up a window with text boxes for the user to enter the start location and stop location. Clicking the run button will calculate the values between start and stop, and write them to a text file (for Location it will be C:\Location.txt | for Beam it will be C:\Beam.txt).

This version has some input validation. For the Location screen users cannot enter an X-coor > 139, and cannot enter a Y-coor > 11.

For Beam the user canot enter a Section# > 139.

Try it out and see if it meets your basic requirements.

If you want me to develop this further, then I'll need detailed info regarding what needs to be changed/added.

[astra84]

Hi Cregan,

Thanks for all the hardwork, But for some reason it doesnt seem to work.

I have attached a screen dump of the error.

Regards Chris

[CreganTur]

Hi Cregan,

Thanks for all the hardwork, But for some reason it doesnt seem to work.

I have attached a screen dump of the error.

Regards Chris

It's probably due to Win Vista's rediculous security features.

Make certain that you have put the database into your Trusted folder. That will re-enable the blocked content: in this case, the ability for VBA to create a text file.

[astra84]

Hi,

That works fine, Thank you very much. Could you make it possible to edit the MHA.

The location setup doesnt work either, or is that because you havent done it yet.

Thanks again

[CreganTur]

Could you make it possible to edit the MHA.
You can edit the MHA. Just type over the exisitng value with whatever value you want. I just set "11B2" as a default since that's the MHA you provided in your text file. This is not a hard default either- the database will remember the MHA that you last used. I can remove this if needed.


The location setup doesnt work either

Define "doesn't work". What values are you using?

Try:
Start:
Rack - 101
X-coor - 001
Y-coor - 01

Stop:
Rack - 101
X-coor - 100
Y-coor - 04

Then click run.

Doing this gives me a text file with 401 lines of text information, where the X-coor, Y-coor, and section values increment correctly. I had to make an educated guess on how to handle incrementation with Y-coor values since you did not have an example file.

Can you provide me with some specific values that you are using so I can test them on my end?

[astra84]

Randy,

Sorry it now works fine.

Thank you very much for all the hard work you have put into this project. I only wish i had the know how so i could of done this myself.

I will take this in to work tomorrow and trail it, Hopefully it wont need any modifications.

Thanks again,

Regards Chris

[CreganTur]

Randy,

Sorry it now works fine.

No need to appologize- I should have provided some instructions when I posted the beta version.


Thank you very much for all the hard work you have put into this project. I only wish i had the know how so i could of done this myself.

Be sure to take a look at the code, it might help you gain an understanding of how VBA works. I learned mostly by picking apart other people's code.


I will take this in to work tomorrow and trail it, Hopefully it wont need any modifications.

Let me know if it does and you need help with them.

I haven't done anything intvolving incrementing Racks (example: Start Rack 101, Stop Rack 103), but I don't know if that's something that you need the program to do. Plus there are a few holes in the data validation. Also, a change will need to be made to the filepath of the created text file, unless you want the text file (C:\Location & C:\Beam) to be overwritten every time you run the macro.

Good Luck:thumb

[astra84]

Hi Randy,

I have tested the coding at work and the Location setup works fine, However the beam setup does not finish at the stop section it is never ending. I have had a look at the coding myself and tried to copy a part of the location coding but that was unsucessful.

Could you please help with this

Thanks

[CreganTur]

However the beam setup does not finish at the stop section it is never ending.

I need to know the exact start/stop coordinates you used when you tested so I can duplicate it on my end.

[astra84]

Hi Randy,

Just as a trail is used

101 - 001 - 01
to
101 - 004 - 01

I used the default entries on the other fields, Once RUN is clicked the "complete" window does not open and access crashes, If you open the txt file from c: drive it runs into the hundreds of thousands. I will attach a screen dump

Regards Chris

[astra84]

Hi Randy,

Please see the attached file,

I went from 001 to 004 and it ended up generating the following results

MHA Rack Section Y-coor Type MaxWght

11B2 101 001 01 .1 0.1
THROUGH TO
11B2 101 3648022 01 .1 0.1

Regards Chris

[CreganTur]

Got it fixed. Use the attached database- it works correctly now.

FYI, here are the details of the error:

The error occured in this section of code:
Do
SectionStart = SectionStart + 3
SectionCheck = SectionStart
If Len(SectionStart) = 1 Then
SectionStart = "00" & SectionStart
ElseIf Len(SectionStart) = 2 Then
SectionStart = "0" & SectionStart
End If
txtfile.Writeline (MHA & vbTab & RackStart & vbTab & SectionStart & vbTab & YStart _
& vbTab & bType & vbTab & MaxWght)
Loop Until SectionCheck = SectionStop

Using the coordinates you tested with, SectionCheck = "4", but SectionStop = "004".

The error was because SectionCheck was missing the leading zeros. Because of this SectionCheck could never = SectionStop, so the loop continues to infinity:stars:

By adding the leading zeros in, the code works because now SectionCheck can = SectionStop.

Corrected code:

Do
SectionStart = SectionStart + 3
SectionCheck = SectionStart
If Len(SectionCheck) = 1 Then
SectionCheck = "00" & SectionCheck
ElseIf Len(SectionCheck) = 2 Then
SectionCheck = "0" & SectionCheck
End If
If Len(SectionStart) = 1 Then
SectionStart = "00" & SectionStart
ElseIf Len(SectionStart) = 2 Then
SectionStart = "0" & SectionStart
End If
txtfile.Writeline (MHA & vbTab & RackStart & vbTab & SectionStart & vbTab & YStart _
& vbTab & bType & vbTab & MaxWght)
Loop Until SectionCheck = SectionStop

[astra84]

Hi Randy,

I tried it again today before seeing your revised code. I entered just the number 4 and it works fine.

Thanks again

Regards Chris

[CreganTur]

After some more research I finally found a way to calculate Location with a math function, instead of using 47 Select Case steps:guitar2:

Below are the 2 procedures that allow me to correctly calculate the location based on any number provided. It also returns the value as a String (since leading zeros are required by Astra for this txt file).

Function Ceiling(Number As Double) As Long
Ceiling = -Int(-Number)
End Function

Sub LocationCount(XStart As Integer, Section As String)
Dim i As Integer
i = XStart
If Ceiling((i - 1) / 3) = ((i - 1) / 3) Then
If Len(CStr(i)) = 1 Then
Section = "00" & i
ElseIf Len(CStr(i)) = 2 Then
Section = "0" & i
End If
ElseIf Ceiling((i - 2) / 3) = ((i - 2) / 3) Then
If Len(CStr(i)) = 1 Then
Section = "00" & i-1
ElseIf Len(CStr(i)) = 2 Then
Section = "0" & i-1
End If
Else
If Len(CStr(i)) = 1 Then
Section = "00" & i-2
ElseIf Len(CStr(i)) = 2 Then
Section = "0" & i-2
End If
End If

End Sub

Astra, I've attached a new version of the RackForms database that incorporates this new procedure into the Location setup. What this means is that you can calculate the number of any Location you want, no matter what X-coor you use. This also means that you won't have to write more Select Case steps if you want to calculate Locations above X-coor 139.