UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA, Dlookup With Forms And Tables., Access 2010    
 
   
arossijr
post Dec 27 2017, 05:58 PM
Post#1



Posts: 2
Joined: 8-December 17



Good Evening, Hopefully I have chosen the correct category for this question. Also Thank You in advance for any and all answers/advice!!
I have gotten some great headway in my MS Access building with the knowledge on this site!!

So here it goes:
I am working on making a "Wage Master" in MS Access, this was originally a MS Excel Spreadsheet.
I have my tables created and updating Fields as I go. Currently I am working on a Form based off a Table that needs to pull data from another table.
The one thing that I am currently trying to get to work is have a Text Box be filled in when a Employee's name is chosen from a Combo Box, using an After Update Event.
I tried to use some info from Northwinds 2007 but it is not working. Mainly the Dlookup.
The Following breakdown is below:
Table: EmployeeInfo
Table: EmpWorkSched1
Form: EmpWorkSched1

The form has numerous fields BUT I will list the ones that I am working with.
Form: EmpWorkSched1
Field: EmployeeName - ComboBox with a SQL/Query in Row Source to Combine First Name and Last Name from the Table: EmployeeInfo ( SELECT EmployeeInfo.EmployeeID,[EmpFirstName] & " " &[EmpLastName] AS Expr1 FROM EmployeeInfo; )
Field: EmployeeName - ComboBox AfterUpdate using VB: Me![EmployeeRate = EmployeeRate(Me![EmployeeName])
EmployeeRate = DlookUP("EmployeeRate", "EmployeeInfo", EmployeeName ='" & Forms!EmpWorkSched1!EmployeeName & "'")
Field: EmployeeRate (The Text Box is what I would like to Auto Fill with the AfterUpdate Event on EmployeeName combo box.

When the VB runs I am getting a Run Time Error '13' Type Mismatch, when I debug the Me![EmployeeRate] = EmployeeRate(Me![EmployeeName]) is highlighted.

Now I realized when I first started working the DLookup that I could not "Match" the EmployeeName ComboBox entry since it was 2 Fields from EmployeeInfo be brought together. So I added a Field in the EmployeeInfo Table called EmpFullName which is running the same SQL/Query in the Row Source on the table. But that is not updating after entry into the table directly...

Ok so the breakdown on Table: EmployeeInfo
EmployeeID = AutoNumber (Primary Key)
EmpFirstName = Text
EmpLastName = Text
EmpPosition = Text /ComboBox = Row Source ="Counter Person";"Driver"
EmpPayRate = Currency
EmpFullName = Text/ComboBox = Row Source = SELECT EmployeeInfo.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS Expr1 FROM EmployeeInfo;

The Breakdown on Table: EmpWorkSched1 is well about 20-25 Fields... so the fields that I am trying to work with at this time will be listed.
EmployeeName = Text/ComboBox = Row Source = SELECT EmployeeInfo.EmployeeID, [EmpFirstName] & " " & [EmpLastName] AS Expr1 FROM EmployeeInfo;
EmployeeRate = Currency

Now as I was typing this out I forgot to change the EmpRate to currency in the table... I made that change. But I am still getting the same error...

I will gladly send the file via Email if requested... Any advice is greatly appreciated. I have tried Google, MS Office, Here of course and so on. I work and learn a lot better hands on and knee deep...

Thanks again!





Go to the top of the page
 
moke123
post Dec 27 2017, 08:26 PM
Post#2



Posts: 1,323
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



QUOTE
Now I realized when I first started working the DLookup that I could not "Match" the EmployeeName ComboBox entry since it was 2 Fields from EmployeeInfo be brought together. So I added a Field in the EmployeeInfo Table called EmpFullName which is running the same SQL/Query in the Row Source on the table. But that is not updating after entry into the table directly...

You should be using the employeeID as your criteria, not a concatenated name. I have 3 Smiths that work in my office and 2 of them have the same first name. You also should not be storing that in a table as you can always concatenate a name for display purposes when needed. Since the rate is in the same table as the name you can include it in your rowsource of the combo and then in after update set the field value to the combobox column value. But what happens when someone changes positions or gets a raise? What then happens to your historical info? I have a feeling that your tables need to be reworked and normalized.
Go to the top of the page
 
arossijr
post Jan 9 2018, 09:05 PM
Post#3



Posts: 2
Joined: 8-December 17



Moke123,

Thank you for replying and giving me some much needed information and 2nd set of eye....
This database is luckily in the start up stages. So much so that I need to get back to it asap. I have about 3 or 4 in progress... Never thought about the EmployeeID to do some of the work.

Again thank you for the information!! and again Sorry it took me so long to reply!

Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th October 2018 - 01:16 PM