Converting a Payroll Application

[crimmelcp]

To All:
First Post to this forum-
I am trying to convert a payroll application that i wrote in another software to Access.

My other application can calculate federal and state taxes, deductions,net pay and write checks. It has been in use for over 13 years.

Someone ask if I could convert it to Access.
I can key in time,calculate federal,state,FIT,and deductions.
My question is:
I am trying to develop a form (see attached JPG)
An alphebet bar (A-Z
when te user selects a letter it will show all employees that their last name begins with that letter. If the user selects a name then all of the employees info will show.

Not sure how to do this in Access.

Please see attached JPG

Any help would be appreciated
Thanks
Charlie Crimmel

 

[pbaldy]

Neither is too tricky. For the buttons, either set the Filter property or change the recordsource to "SELECT ... WHERE ... Like 'A*'".

To show the selected record, if you play with either the combo or list box wizards using the 3rd option, "Find a record..." it will build the relevant code for you.

 

[crimmelcp]

Thanks I have tried 2 syntax and get errors on Both

Thanks I have tried 2 syntax and get errors on Both Give errors
Any idea on what the syntax might be and how to refresh the form after the filter.


=Left([LNAME],1) Like "A"


also

SELECT [Empdata]![LNAME], [Empdata]![FNAME], [Empdata]![EMPNUM], [Empdata]![BRSNO], [Empdata]![CRAFT], [Empdata]![CRDESC]
FROM Empdata
'WHERE (((Left([LNAME],1))="A"));

 

[pbaldy]

What error do you get? The SQL looks fine to me. If you're building that SQL in VBA, you'd want single quotes around the A.

 

[crimmelcp]

All I get is Syntax error

I have tried different things
Right now I just get syntax error
Some lines are commented out where I have tried sifferent things
CODE:

Private Sub LetA_Click()
Dim str_Let As String
DoCmd.SetWarnings False
str_Let = "A"
'DoCmd.RunQuery "LetterA"
SELECT Empdata.LNAME, Empdata.FNAME, Empdata.EMPNUM, Empdata.BRSNO, Empdata.CRAFT, Empdata.CRDESC
FROM Empdata
WHERE (((Left([LNAME],1))= 'A'));

'DoCmd.ApplyFilter LetterA, Left([LNAME], 1) = "A"

DoCmd.SetWarnings True
End Sub

 

[pbaldy]

Ah, your SQL is dangling in space (you haven't told Access what to do with it). Try this:

Me.ListBoxName.RowSource = "SELECT Empdata.LNAME, Empdata.FNAME, mpdata.EMPNUM, Empdata.BRSNO, Empdata.CRAFT, Empdata.CRDESC FROM Empdata WHERE ((Left([LNAME],1))= 'A'));

You don't need any of the rest of it. If/when we get that working, I'll show you a slicker way of doing it.

 

[crimmelcp]

Paul:
Attached is a zip file

The form that I am working on is test empdata

 

[pbaldy]

No zip. Make sure it's under the size limit. Compacting before zipping will usually accomplish that.

 

[crimmelcp]

We will try again

I stripped all the other forms out

 

[pbaldy]

Ah, your picture showed a listbox, but you're just using one form. This works:

Me.RecordSource = "SELECT LNAME, FNAME, EMPNUM, BRSNO, CRAFT, CRDESC FROM Empdata WHERE Left([LNAME],1)= 'A';"

I'll post a slick way to do them all in a minute.

 

[pbaldy]

This is a little cleaner than what you would have ended up with. Note that I just have one function, called from each label (I only set up A & B for testing).

I also note you'll need to add a couple of fields to the SQL.

 

[crimmelcp]

Thanks

A listbox would probably be better. (If I knew how to do one)

That Would save the rest of my form for the selected employee information


For the last 14 years, I have programmed in other languages and just starting with Access

Thanks
Charlie Crimmel

 

[crimmelcp]

Got List Box working

I got the List box working with the Alpabet Bar
When I select the letter, only those employees with the last name starting with that letter shows.

When i select a name from the listbox I want to find the record and show the employee info on the right

any ideas?

see attached zip

Thanks
Charlie Crimmel

 

[pbaldy]

Private Sub Employee_DblClick(Cancel As Integer)
  Dim rs                 As Object

  Set rs = Me.Recordset.Clone
  rs.FindFirst "EMPNUM = '" & Me.Employee.Column(2) & "'"
  If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I note that the fields are in a different order when the form first loads, which means this will only work after a letter has been clicked on. Putting them in the same order when the form loads should make it work no matter what.