Simple programming using macros

[captain crash]

Hello, I am learning access and VB as i go along so please forgive any obvious questions i may be asking.

I have a personnel table where records are entered in via a form.
Some fields are: Last name, first name, and title (position in the company).
Every time someone gets promoted their title changes and as a result their pay is increased.

For billing purposes i need to keep track of their old position as well. To do this i have added two fields to the table: Start, End.
Start records the date when the time is created using the now() operator.

Whenever i create a new employee i would like a macro to check if this person is already in the employee list, and if so then use the now() operator to fill in the end field of his old entry. This way i will have multiple entries for the same employee but with different titles, and hence different billing rates.

My sub-question here which will greatly help me in the rest of my project is how do you refer to table names and fields when writing VB code, using forms.
If i need to search a list of entries what is the equivalent of using a row pointer in excell and then implementing the row pointer so it searches the whole list.

I've tried researching this but am lost. Help!

Thanks in advance.

 

[captain crash]

can you elaborate? Are you saying i should use SQL instead of VB?

 

[FoFa]

I think how you approach this depends more on how you want to "see" if someone already exist.

Are you planning on a straight comparison, example first name = first name and last name = last name? Or are you going to get more complicated than that?

You could use a simple vMyLong = DCOUNT("EmpMasrtID","EmpMstrTable","FirstName = '" & Me.Firstname & "' AND Lastname = '" & Me.LastName & "'")

Then if vMyLong <> 0 that name exists.

Just as one thought.

 

[georgedwilkinson]

Captain Crash,

I would ask you to reconsider your design. It is not normalized and puts you on the path to destruction.

A better approach is to have multiple tables representing the various aspects of being an employee. One of the tables is the stuff pertinent to being a person...their name, ssn, DOB, possibly DOD, etc.

Another of the tables would be about the person's position in the company. This would have a reference to the Person information (via PersonID FK), the position, the to and from dates, the status of that position, text related to the position, etc.

Depending on how complex the system is/is likely to get, you might have a Position table with approved/recommended positions and the budget information regarding each position. You would keep a reference to this table in the person position table mentioned above.

Since I'm assuming this is Access and not SQL, it makes it a little more difficult to manage new positions for the employee. Essentially, you'll want to "invalidate" the previous position when you assign a new position to a person. You could do that with VBA (preferred) or via an Access Macro (no help from me, though).

There is much, much more but this should give you a general idea of how to proceed with this particular problem. Under no circumstances should you keep duplicate information in your employee table.

 

[captain crash]

thanks for the help, i will take your suggestions under consideration and get to work. I will post my results when im done.

 

[georgedwilkinson]

Sounds good. Let us know if you need help.

 

[Mike375]

What George said about tables.

You might also make a calculated field in a query that joins first and last name as a single entity and include any other data such as date of birth, suburby or whatever. A query that has a field based on [FirstName] & " " & [LastName] & " " & [City] will combine them in on field as a single unit.

For example you might have three people with LastName of Andrews. However, they won't all be John Andrews Huntsville.

A simple query, macro or code will open a related form for matching records but the point is that LastName is not the criteria but rather the combination of FirstName, LastName and whatever else you include.

 

[captain crash]

Hello again, I just wanted to say that this project is really overwhelming and im working on it as much as I can spare time. Haven't forgotten about this thread or anything, the solution might take a while (in case anyone was wondering) Dont want to break Forums protocol.
FOR THE HORDE!

 

[georgedwilkinson]

FOR THE HORDE!

More specifics please: Realm, race, class, hours played per week, etc.

 

[captain crash]

haha ok:
Crash, Level 67 Troll Hunter, Burning Blade server. Pet: White tiger from winterspring. No guild. Love the franchise, played all warcraft games and read all books. Currently not playing WoW.
Yourself?

 

[georgedwilkinson]

Tons on different servers and factions.

I guess my main (if you wanna call it that) is Dorheroa, Level 63 Draenei Hunter, Shu'Halo server, Pet: brown Ravager (I never play him). I spend a lot more time in the AH with my level 1 auction mules than I do playing because I love the math/data manipulation of making a huge fortune without doing anything but buying and selling.

All my Horde toons are Blood Elves because I like looking at them for hours at a time.

Let me know if you need help with your problem...I can help, and Tony and FoFa are both incredibly capable, too.

 

[captain crash]

Well, i'm currently researching recordsets because what i ideally want to do is be able to manipulate any field in any table using visual Basic, through forms. This is more my field of play. Something along the lines of !me.field or

[mytablename].... but its the syntax thats holding me back. Its my first job right out of school so im trying to learn as fast as i can!