December 19, 2011 at 7:53 am
Hi,
I have a requirement to autopopulate a textbox (DIVISION_CD) based on the value in another textbox(LINE_CD).
A table - DIVISION already exists which contains the 2 columns - LINE_CD and DIVISION_CD. How can I use the DIVISION table as a lookup table to autopopulate the value in the DIVISION_CD textbox based on the matching value in the LINE_CD textbox.
Would somebody know on how to about this ? I have googled regarding this but have not been able to find any solution. Any suggestions would be appreciated.
Further info:
------------------
Please find the table structure of the DIVISION table below:
----------------------------------------------
CREATETABLE [dbo].[DIVISION](
[LINE_CD] [varchar](2)NULL,
[DIVISION_CD] [varchar](4)NULL
)ON [PRIMARY]
----------------------------------------------
The sample data for the above table:
----------------------------------
INSERT INTO DIVISION
VALUES('01', 'A432')
INSERT INTO DIVISION
VALUES('02', 'A442')
INSERT INTO DIVISION
VALUES('03', 'A452')
INSERT INTO DIVISION
VALUES('04', 'A462')
INSERT INTO DIVISION
VALUES('05', 'A472')
----------------------------------
The textbox - DIVISION_CD needs to be populated with the value based on the matching value of the LINE_CD textbox using the above table as a lookup table for looking up the values of DIVISION_CD column.
I hope I am able to explain my situation.
Thanks,
Paul
December 19, 2011 at 2:48 pm
Hi Paul
I am comfortable using VBA, so my solution would be to add an AfterUpdate event on the LINE_CD control with something simple like a DLookup, using it to set the value of DIVISION_CD.
As always, there is probably more than one solution. If VBA is not your thing, you may be able to use the DefaultValue property of the DIVISION_CD control and set it using the DLookup function there as well. The problem with that, I suspect, will be forcing it to update if LINE_CD is changed.
The VBA solution is the only viable method which comes to mind for me. It is relatively simple too if you are as yet to make forays into this aspect of Access.
I'm not sure of your experience with Access, but I remember this as being an early problem for me to resolve and the hardest part about it was getting the DLookup right. If this is you, I suggest experimenting with the syntax in a query.
Another method for learning how to use DLookup would be to dim a variable in the AfterUpdate event first. Set this to the result of your DLookup and then set a breakpoint on the event so that you can step through the code. That way you can deal with any syntax errors and easily check the value being returned by DLookup. Once you are happy with the result, you can remove the variable and set the control value directly if you wish.
Have a go and post back if you have any questions. I'll be more than happy to guide you through any difficulties you have but I would rather you had a go first as you will learn more from it than if I just write you a piece of code.
Regards
Rowan
December 20, 2011 at 3:19 am
ProofOfLife (12/19/2011)
Hi PaulI am comfortable using VBA, so my solution would be to add an AfterUpdate event on the LINE_CD control with something simple like a DLookup, using it to set the value of DIVISION_CD.
As always, there is probably more than one solution. If VBA is not your thing, you may be able to use the DefaultValue property of the DIVISION_CD control and set it using the DLookup function there as well. The problem with that, I suspect, will be forcing it to update if LINE_CD is changed.
The VBA solution is the only viable method which comes to mind for me. It is relatively simple too if you are as yet to make forays into this aspect of Access.
I'm not sure of your experience with Access, but I remember this as being an early problem for me to resolve and the hardest part about it was getting the DLookup right. If this is you, I suggest experimenting with the syntax in a query.
Another method for learning how to use DLookup would be to dim a variable in the AfterUpdate event first. Set this to the result of your DLookup and then set a breakpoint on the event so that you can step through the code. That way you can deal with any syntax errors and easily check the value being returned by DLookup. Once you are happy with the result, you can remove the variable and set the control value directly if you wish.
Have a go and post back if you have any questions. I'll be more than happy to guide you through any difficulties you have but I would rather you had a go first as you will learn more from it than if I just write you a piece of code.
Regards
Rowan
Thanks Rowan ! I have tried doing what you mentioned using the Afterupdate event:
Private Sub Text41_AfterUpdate()
Me.DIVISION_CD = DLookup("LINE_CD", "dbo_DIVISION", "Reference = " & Me.Reference)
End Sub
But nothjing happens after that and there are no values being displayed in the DIVISION_CD textbox. Do I need to add some code in the DIVISION_CD textbox to retrieve the corresponding value. Could you please give some inputs on this ?
December 20, 2011 at 5:55 am
Wrong object, correct event.
December 20, 2011 at 8:20 am
Ninja's_RGR'us (12/20/2011)
Wrong object, correct event.
Hi Ninja,
Thanks, I was able to find out the correct syntax but this still doesnt work and gives an error- #Name?
I am trying to autopopulate a textbox, Textbox45 from another textbox, Textbox41(already autopopulated) and have entered this code in the control source property of Textbox45-
------------------------
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = ' " & [Me].[Text41] & " ' ")
------------------------
and then added this code in the After Update Event property of Textbox41-
---------------
Private Sub Text41_AfterUpdate()
[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = ' " & [Me].[Text41] & " ' ")
End Sub
---------------
Could you please tell what is it that I am doing wrong now ?
December 20, 2011 at 8:41 am
You still have those values in the combobox right? Just get them there with the column index and you'll be fine.
December 20, 2011 at 8:58 am
Ninja's_RGR'us (12/20/2011)
You still have those values in the combobox right? Just get them there with the column index and you'll be fine.
I didnt understand you, I cannot bring the values for the DIVISION_CD in the combo box as that brings out duplicates for other columns. Is that what you meant ?
I need to use the look up table- DIVISION to get the values for the DIVISION column. Combo box is out of question here.
December 20, 2011 at 9:17 am
Then tweak the dlookup. Not sure enough of the syntax to help you out here.
December 20, 2011 at 9:22 am
Ninja's_RGR'us (12/20/2011)
Then tweak the dlookup. Not sure enough of the syntax to help you out here.
Well, that is the only problem and the reason for my query. This is what I have been trying to do all day without success, no worries thanks.
December 20, 2011 at 5:41 pm
Been there, done that. The only way you really learn something is by whakking around it for hours :hehe:.
December 21, 2011 at 3:54 am
There are two events that will need the TextBox 'DIVISION_CD' to be changed:
1. When the user changes the value of the TextBox 'LINE_CD' ('LINE_CD_AfterUpdate' event).
2. When the current record changes ('Form_Current' event).
Here's a solution:
Sub Update_DIVISION_CD()
Me.DIVISION_CD.value = DLookup("DIVISION_CD", "DIVISION", "LINE_CD = '" & Me.LINE_CD.value & "'")
End Sub
Private Sub Form_Current()
Update_DIVISION_CD
End Sub
Private Sub LINE_CD_AfterUpdate()
Update_DIVISION_CD
End Sub
Have a nice day!
December 21, 2011 at 4:36 am
Hi Paul
My apologies for not having followed up earlier - time zones and a very busy day getting things finished before going on holiday.
OK, you are actually very close. The after update event on Text41 should be all you need ie
[/code]
Private Sub Text41_AfterUpdate()
[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = ' " & [Me].[Text41] & " ' ")
End Sub
[/code]
In addition, alter the control source of Text45 to the DIVISION_CD coulmn from your record source - you don't need the DLookup here. This will just confuse things and you are likely just to end up with the same value as you started.
A DLookup is basically a VBA SELECT statement, the above converting to:
[/code]
Select DIVISION_CD From dbo_DIVISION Where LINE_CD = 'the value of [Me].[Text41]'
[/Code]
You should be able to replace the 'the value of [Me].[Text41]' in this SQL statement with an actual value and have a DIVISION_CD returned. If you don't, then your syntax is wrong or you have entered an incorrect value in the Where clause.
If you are having problems getting the correct value to display in Text45, put a break point on the line of code in the event. run the form and change Text41. The code processing will stop at the breakpoint and you can hold your mouse pointer ove the [Me].[Text41] part to see what value it is getting.
This brings a question to mind - Text41 is a plain text control not a combo? If it is a combo you have another issues to deal with but I won't go into those unless I need to.
A tip on form building. I'm a bit fussy when it comes to my controls and I avoid leaving control names as the default, especially if I am programming events. Once you have had to make changes to someone else's forms, where the controls have not been given meaningful names you will understand why. Give the control a meaningful name before creating any events, and the events will be correctly named. If you change the control names later, you have to manually edit any event names.
I hope this is of some assistance. My apologies again.
Regards
Rowan
December 21, 2011 at 4:53 am
Hi Paul
I see a post has slipped in while I have been preparing my reply.
Personally I like to keep my code simple and direct so I wouldn't write the dlookup into a sub routine. But every programmer develops their own individual style based on how they have been taught and the learning they have done through solving problems. So this method is not at all wrong - it is simply a different style.
But the big difference here is the inclusion of the form_current event. Given that you are only wanting to update 45 when 41 changes, I don't see it as necessary the run the dlookup every time the form is opened or whenever the focus leaves one record and moves to another which is what the form_current event is about. Too many trips to the database for my taste.
All the best.
Regards
Rowan
December 21, 2011 at 5:09 am
ProofOfLife (12/21/2011)
But the big difference here is the inclusion of the form_current event. Given that you are only wanting to update 45 when 41 changes, I don't see it as necessary the run the dlookup every time the form is opened or whenever the focus leaves one record and moves to another which is what the form_current event is about.
This is only true if the values fetched from the lookup table are stored into the table that serves as RecordSource for the form, which was never said.
It's also the reason why I created a distinct Sub procedure that can be called from both events (Form_Current and TextBox_Update), avoiding to duplicate its code in both event handler procedures.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply