Autofill, afterupdate question 3

[gscma1]

I have a form 'frmHours' whereby the user enters data about an employees hours worked and cash required each week.

cboEmpId
cboWeekNo
cboPeriod
Hours
Cash

is it possible to have a text field update with the employee name, when the empid combo box has been selected?

also 'tblemployees' contains details of empid, forname, surname, wage rate, cash required weekly, etc...

is it possible for my 'frmHours' to autofill the cash field with the amount of cash they require weekly from 'tblEmployees'???

Many Thanks

 

[Remou]

Have a loo at DLookUp. It is suitable for both these cases. An alternative is to have extra columns in the combo, which can be referenced by number, starting at 0:
[tt]=Me.cboEmpId.Column(n)[/tt]

 

[gscma1]

I have attempted using dlookup to update the employee forename from the empid; this is the code:

can you spot any problems because i'm baffled at what i might have done wrong.

Private Sub Forename_BeforeUpdate(Cancel As Integer)
=DLookUp("[Forename]","tblEmployees","[EmpId]=forms!FormHours![EmpId]")
End Sub

I keep getting compile errors

 

[ItIsHardToProgram]

First you need to trap the variable, in something, which i believe you do but you forgot to input, secondly, I usualy do it like this.

Private sub Forename_Beforeupdate(Cancel as integer)
dim criteria as string
Criteria = Forms!Formshour!EmpId

If isnull(Dlookup("Forename", "tblEmployees", "[EmpId] = " & criteria) Then
Msgbox "No matching records"
Else
TrapValue = Dlookup("Forename", "tblEmployees", "[EmpId] = " & criteria)
End if
End sub

You could just store the value, but you need to test if it is null in your error trapping.

hope it helps

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[gscma1]

trap the variable, do you mean choose the employee id?

I decided to set up the code as you have suggested, however although i receive no errors nothing appears in the text box when the empid is selected.

baffled

i have checked all my field names are correct

 

[Remou]

There is no need to use an event, simply use a text box and set the control source to:
[tt]=DLookUp("[Forename]","tblEmployees","[EmpId]=" & forms!FormHours![EmpId])[/tt]

That is assuming EmpID is numeric. If it is text, you will need some single quotes.

If you did want to use an event, you would say:

Private Sub [b]EmpID_After[/b]Update(Cancel As Integer)
Me.Forename=DLookUp("[Forename]","tblEmployees","[EmpId]=" & Me.[EmpId])
End Sub

If you want pretty, you can use the caption property of a label.

 

[gscma1]

I have tried both, setting the control source to what you suggested and i have tried to use an event, both retrieve no results.

Thanks for your help but i think i'll give up it just doesnt seem to want to work. it wasn't essential just would have made the interface easier to use.

 

[randy700]

Before you give up, try posting the exact code you are using. Someone here can help you correct it.



Randy

 

[Remou]

Never give up!
Check the table names, field names and field types. For example, EmpID may be text, if it is, try this in a textbox:

=DLookUp("[Forename]","tblEmployees","[EmpId]='" & [EmpId] & "'")

Or add similar quotes to the event.

 

[ItIsHardToProgram]

setting the control source to what you suggested

we have never mentioned control source, in fact we are talking about Vbase coding...

This is a very standart procedure... Sorry I did not see that it was a beforeupdate... you indeed need it in a after_update event....

If you could tell us exactly what you want (is this event on a button etc.....) explaining us in details, we could guide you step by step... I believe you need guidance.

in fact if you place it in the txtbox property event it is only

=DLookUp("[Forename]","tblEmployees","[EmpId]='" & [EmpId] & "'")

As Remou mentioned

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[gscma1]

Ok, well in reponse to ItIsHardToProgram, exactly what i want is as follows:

when i update the empid combo box i want another text box to update with the forename of this employee and similarly i would like an adjacent textbox to autofill with the surname.

in reponse to Remou, i've checked my table names and field names they are definitely correct, furthermore so are the field types. empid is a number field.

Many thanks guys!

 

[Remou]

ItIsHardToProgram
In my book it is VBA, not Vbase .

gscma1
You are not getting any result, is that correct? This implies that EmpID does not hold anything or does not hold what you expect. There are a couple of things you could look at. If you put:

[tt]=DLookUp("[Forename]","tblEmployees")[/tt]

as the control source, it should return something if all employees have a forename, because there is no criteria.

A further check, this:

[tt]=[EmpId][/tt]

as the control source in a textbox, should show what EmpID contains.

 

[randy700]

Assuming the first column of your combo box is EmpID....

Go to design view.
Right click on the text box to display the properties box.
Select the Data tab.
On the Control Source line, type:

=dlookup("Forename", "tblEmployees", "EmpID = " & [b][COLOR=red]YourComboBoxName[/color].Column(0)[/b])

Substitute "surname" for "forename" in your other text box.


Randy

 

[ItIsHardToProgram]

There are more then one ways to achieve this... probably the best(easiest) way, depending on why you are doing this.....

[blue]Firstly[/blue] unbound all txtboxes that are in use, employee name, Forename, surename
Lets say txtboxes are named TxtForeName, TxtSureName, EmpID

[blue]Secondly[/blue] Simply press Ctrl+G this should invite a vbase window in it you should enter the code that we already mentioned:

Private Sub EmpID_AfterUpdate()
Me.txtForename=DLookUp("[b][Forename][/b]","tblEmployees","[b][EmpId][/b]=" & Me.[EmpId])
Me.txtSurname=DLookUp("[b][Surename][/b]","tblEmployees","[b][EmpId][/b]=" & Me.[EmpId])
End Sub

Finaly this code will execute only after you have entered a specific value in it and lost focus on the specific txtbox or do any other command

**Note** This is asuming that your table fields are Forename, Surename And EmpID, any other name would need a change in the Bold part of the code

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

I read to fast and did not see it was a combobox, indeed you will need to add the .Column(0) parameter to the code you have in the event... and if you go through the vbase window you will also need to add it

Private Sub EmpID_AfterUpdate()
Me.txtForename=DLookUp("[Forename]","tblEmployees","[EmpId]=" & Me.[EmpId].Column(0))
Me.txtSurname=DLookUp("[Surename]","tblEmployees","[EmpId]=" & Me.[EmpId].Column(0))
End Sub

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Remou]

ItIsHardToProgram
As far as I recall, a Combobox returns the value of the Bound Column, so if you wish to reference this column, you do not need to specify it.

 

[ItIsHardToProgram]

Remou

Thank you very much for the clarification. I am nothing close to being totaly acquinted with access, trying my best to help.

gscma1

How is everything going?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[gscma1]

Thanks a lot all you guys, i'm not sure if you are american or english, but i'm from england and its 5 o'clock so i'm going to get my ass home and start again on this 2moro, so i'll keep u posted once i try a few more ways.

thanks for all your help, its much appreciated i can tell you!

 

[ItIsHardToProgram]

gscma1

In all means the code we have provided is correct and should work, if it does not it is either a problem in the communication or maybe you are misleading something.

have a nice evening, take care.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[gscma1]

Hi, It now works, very well and I have employed the same principal to have it update my Cash column as each individual employee gets a specific or no amount of cash each week, so i am very pleased.

however one problem i have is that there approximately 40 people who get paid each week. and when i move through the records for the week it only keeps the name and cash total for the last person i input. so although i have the correct id and the correct hours total, the name and cash total stay the same as the last person i input.

any ideas?