using AfterUpdate to search and update several fields 3

[SeanWcisel]

Hi guys!

I've got a form with about thirty text boxes on it. I need the user to be able to type a value in one of them (always the same box), press enter, and have the other values populate depending on the values associated with the user input. I have that field linked to the appropriate table, and the values in that column are unique key values.

I'm fairly new to VB, but I'm pretty experienced with programming in general, so don't hold back on me .

Thanks a ton! I checked google, and gave these forums a once over, but this problem seems to be a little specific to be googlable (ignoring the spell check's protest on that word ).

Thanks again,
Sean

 

[PHV]

Are the 29 other values coming from the same table ?
Anyway, a crude way is to call 29 times the DLookUp function, but often a ComboBox is a better choice as you may retrieve hidden values with the Column property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeanWcisel]

Yeah, they're all in the same table (well, they're from different sources, but I've compiled them in one table). I was just reading a post that describes something similar. It was only a few posts down, so I apologize for that. The problem is, I also have next/previous buttons, so I'd like to point to the record itself if possible. Anyone have tips to that end?


And thanks for the quick reply!

Sean

 

[PHV]

Why not having your 30 controls bound to the underlaying table/query's fields and then, in the header section of the form, create a combobox with the wizard (3rd option) ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeanWcisel]

And then just have those fields update when I select an option? That might work, I guess I'm not quite sure how that'll help me actually jump to that record though. The other problem is that this code needs to hold up when there are 10,000+ database entries. I'm trying to avoid using trees if at all possible, not a graceful solution when linked to the key press event .

Anyway, I'd like to actually navigate those items if possible, rather than just loading the values into the fields (and yes, this was a recent realization that makes the title of this thread look dumb ).

Thanks again, we're making progress here

Sean

 

[mrkshpntf]

Sean,

Here is a sample code I used:
----------------------------------------
Private Sub AgentName_AfterUpdate()


Dim strAgentId As String
Dim strManagerName As String
Dim strManagerID As String

strAgentId = Nz(DLookup ("[AgentId]" "EmpMgr", "[AgentName] = '" & Me![AgentName] & "'"))

strManagerID = Nz(DLookup("[ManagerID]", "EmpMgr", "[AgentName] = '" & Me![AgentName] & "'"))

strManagerName = Nz(DLookup("[ManagerName]", "EmpMgr", "[AgentName] = '" & Me![AgentName] & "'"))

Me.AgentID = strAgentId
Me.ManagerName = strManagerName
Me.ManagerID = strManagerID

End Sub
-------------------------------------------------

What I trying to do was, when I entered the Agent's name in one text box and hit enter 3 text boxes, namely Agent's ID, Manager's Name and Manager's ID will get populated (it will be 29 instead of 3 in your case).

This is how to go about it:

The value you enter should be unique.

In the properties of the text box where you enter your value, go to the 'after update' property and click on the command button with "..." symbol. In the 'choose builder' prompt, select 'code builder' and hit OK.

This will bring up the code window. You'll need to type in the code in the space between "Private ...." and "End Sub".

First declare your variables with the Dim statement (you will need one for each of your 29 text box/columns/fields)

Now create one of these (sorry for instructing with an example)

strManagerID = Nz(DLookup("[ManagerID]", "EmpMgr", "[AgentName] = '" & Me![AgentName] & "'"))

for each text box

strManager is the value you want to appear in one of the 29 boxes

EmpMgr is the table name where these values reside

AgentName is the value you enter in the first box.

DO NOT change anything in the syntax except the names of your textboxes and table!

After you have done this for all 29, type this for each 29

Me.AgentID = strAgentId

where again AgentID is one of the 29 boxes you want to get filled, so do it for all 29.

This code worked for me and it should work for you too.

All the best!!

 

[SeanWcisel]

Thanks guys,
Once again, I need to actually navigate to a record. It looks like the macro builder is the way to go. I'm still not sure about the syntax for the expression though. What would I put in the "find what" field to search for something? Let's say I'm looking for customer 199 using field SearchBox, and the Find Record action, can anyone help me put together that syntax?

 

[bendixen]

Sorry to hijack this thread a little but I cannot find where to start a new thread and my question is exactly the same as SeanWcisel's. I'm using the code provided by mrkshpntf (almost exactly!) but am getting a Run-time error '3464'atatype mismatch in criteria expression.

 

[mrkshpntf]

bendixen,

Almost all my fields are set as text. I am sorry but the code I gave is for this datatype.

If it doesn't destroy your data (it happens when you change text to number or vice versa) you can change the datatype to text.

What I would do is: make a copy of your current DB call it trial or something you like and play with it. Transfer the stuff that works to your Actual DB.

Hope this helps.

All the Best!!

 

[bendixen]

mrkshpntf,

My fields are all text. No worries on destroying data, I already made a trial copy first. I've been playing with it all week to no success. Perhaps I'll start a new thread with my specifics (now that I've found where).

bendixen