Update Field Values

[Guest]

I have a form with 4 fields: jobFunction, RACF, Intranet, and Other. The
first is a list box, the second is a text field, the third is a memo field,
and the fourth is a text field. What I want to do is, once a user selects a
record in the jobFunction field (a list box), I want to update the other
three fields based on that value. The data is coming straight from a table.
When a user selects the Branch Manager value in the jobFunction field, for
example, I want to update the other three fields with the data for the Branch
Manager record in the table from whence the data comes. Below is the code I
have tried in VBA, which doesn't work.

'Refresh RACF field after update of jobFunction field
Private Sub jobFunction_AfterUpdate()
Me![RACF] = DLookup("RACF", "standardProfiles", "Me.[jobFunction]='" &
[standardProfiles].[jobTitle] & "'")
End Sub

Thanks in advance for your help, and I hope all the American discussion
group members reading this post have a wonderful but safe Independence Day
weekend!

 

[Guest]

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")

 

[Guest]

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

 

[Guest]

Sorry, I got the last part wrong. Try

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = " &
Form![jobFunction])

Square brackets are optional on single words.

Regards

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")

 

[Guest]

I copied and pasted your code, and it still does not work. I am working in
the Visual Basic window for the form on which these fields reside.

Thanks!

Sorry, I got the last part wrong. Try

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = " &
Form![jobFunction])

Square brackets are optional on single words.

Regards

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")

 

[Guest]

OK, third time lucky:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = '" &
Me![JobFunction] & "'")

Forgot the quotes around JobFunction! If you're cutting and pasting make
sure it is all on one line in VBA.

Regards

I copied and pasted your code, and it still does not work. I am working in
the Visual Basic window for the form on which these fields reside.

Thanks!

Sorry, I got the last part wrong. Try

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = " &
Form![jobFunction])

Square brackets are optional on single words.

Regards

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

:

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")

 

[Guest]

Now I'm getting an error message:
"Runtime error 2448. You can't assign a value to this object."

OK, third time lucky:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = '" &
Me![JobFunction] & "'")

Forgot the quotes around JobFunction! If you're cutting and pasting make
sure it is all on one line in VBA.

Regards

I copied and pasted your code, and it still does not work. I am working in
the Visual Basic window for the form on which these fields reside.

Thanks!

Sorry, I got the last part wrong. Try

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = " &
Form![jobFunction])

Square brackets are optional on single words.

Regards

:

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

:

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")

 

[Guest]

Is it definitely a normal text box you have created for RACF? Is the record
source for the form an updateable dynaset (allow edits)? Is the RACF field in
the standard profiles table a text field? Can't think of anything else that
would cause this.

Regards

Now I'm getting an error message:
"Runtime error 2448. You can't assign a value to this object."

OK, third time lucky:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = '" &
Me![JobFunction] & "'")

Forgot the quotes around JobFunction! If you're cutting and pasting make
sure it is all on one line in VBA.

Regards

I copied and pasted your code, and it still does not work. I am working in
the Visual Basic window for the form on which these fields reside.

Thanks!

:

Sorry, I got the last part wrong. Try

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = " &
Form![jobFunction])

Square brackets are optional on single words.

Regards

:

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

:

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")

 

[Guest]

I found a too easy way to do this. If I use the wizard to place a combo box
or list box in the form's header, then by selecting the right options in the
wizard all the code is written for me, and it works. When I click a selection
in the list box, the form updates the other fields.

Duh!

Is it definitely a normal text box you have created for RACF? Is the record
source for the form an updateable dynaset (allow edits)? Is the RACF field in
the standard profiles table a text field? Can't think of anything else that
would cause this.

Regards

Now I'm getting an error message:
"Runtime error 2448. You can't assign a value to this object."

OK, third time lucky:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = '" &
Me![JobFunction] & "'")

Forgot the quotes around JobFunction! If you're cutting and pasting make
sure it is all on one line in VBA.

Regards

:

I copied and pasted your code, and it still does not work. I am working in
the Visual Basic window for the form on which these fields reside.

Thanks!

:

Sorry, I got the last part wrong. Try

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] = " &
Form![jobFunction])

Square brackets are optional on single words.

Regards

:

Sorry it took me so long to respond ... I was temporarily pulled to another
project. I tried what you suggested, but it didn't work. Then I tried
modifying "RACF" to "[RACF]", since it is the name of a field in the table
I'm trying to pull data from.

Me![RACF] = DLookup("[RACF]", "standardProfiles",
"[jobTitle]=Form![jobFunction]")

That didn't work either. Any ideas? Thanks for trying!

Gwen H

:

I think your criteria is wrong, try:

Me![RACF] = DLookup("RACF", "standardProfiles", "[jobTitle] =
Form![jobFunction]")