Dynamic Form to Table lookup and Populate

[Tezcatlipoca]

Hi All,

I've been struggling with this for a few hours now and can't seem to get it to work. I'll use a simplified example, but the actual requirements are exactly the same:

Ok, I have a table called tblData. This contains three fields; ID (the primary key), Code, and Name.

Now I also have a folder on my computer full of files that follow the format XXXXX - YYYYYYYYYYYYYYY, where XXXXX is a five character code and the Ys are the name of the file. these are all standard Word documents.

I have a form in my database that has a search engine embedded which is pointing at this folder of files. so far, so good.
This search engine can be used to filter the filelist based on a variety of variables, and this works beautifully.

Now underneath this searchbox are two unbound but locked off textboxes, one for the code and one for the filename.
What I want to happen is when you click a file from the list above, the Code box automatically fills out with the code from the filename. So, for example:

Let's say you set your search requirements, clicked search and found the file 'AB001 - Test File.doc' on the list. You then click that file and the locked Code box below fills out with AB001. This part I can get working perfectly.

Now what I need to do is have the database take whatever is in that locked Code box, hunt for it in the tblData table, and, if found, fill out the Name box with whatever is in the table.

Yes, I know this seems an odd way to do things, but for the purposes of this database, stick with me; it's the easiest way to achieve what needs to be done with this database!

So, to recap. Let's say I have two entries in my tblData as follows:

ID: 1
Code: AB001
Name: This is a test

ID: 2
Code: AB002
Name: This is another test


Now the user uses the form to search the folder of files. They find the one they want, in this case 'AB001 - Test File.doc' and click on it. The locked textbox then autofills with 'AB001' and the locked Name textbox fills out with 'This is a test'.

Essentially, I am trying to create a database that will act as a master library of a folder of files. People can use the database to search for files, find the one they want, then access a load of data about that file held in the Access tables. Eventually, they will be able to alter that data and Access will - if need be - rename the original file, but one step at a time.

If there's a better way to achieve what I'm trying to do then I'm happy to consider it, but, if not, how can I get this done?


*** UPDATE***

I think the simplest solution for this is to introduce something like an OnClick event into my filtered search results that tells the form to automatically jump to whichever record in the table has a Code field that equals the code segment of the selected record?
Still not sure how to accomplish this, I think possibly a DLookup command in my VB?.

 

[Tezcatlipoca]

Can anyone point me in the right direction of this, please?

I've tried adding

Me.Name.Value = DLookup("[Name]", "TblFileData", "

 = Me.Code.Value")

to the OnClick event on my listbox of search results, to try to populate the Name box on the form with the correct details, but this just gives a strange 'You have cancelled the previous operation' error.  What operation?

 

[JANR]

Me.Name.Value = DLookup("[Name]", "TblFileData", "

 = Me.Code.Value")[/quote]
 
This assume that "code" is a number and not a string, which it is.
 
Me.Name.Value = DLookup("[Name]", "TblFileData", "[code] ='" & Me.Code.Value & "'")
 
BTW your fieldnames are BAD!! code and name are Access Reserved words!!!!! use something else for objectnames.
 
JR

 

[Tezcatlipoca]

This assume that "code" is a number and not a string, which it is.

Me.Name.Value = DLookup("[Name]", "TblFileData", "

 ='" & Me.Code.Value & "'")
 
BTW your fieldnames are BAD!! code and name are Access Reserved words!!!!! use something else for objectnames.
 
JR[/QUOTE]

Ah! You utter star, JR.  Thank you.  Not only is my lookup working now but I also now understand [I]why[/I] it was failing (I was missing out the '" & at the start and "' at the end to present the data).

Regarding the [Code] and [Name] thing, those are fields in the table 'TblFileData'.  [Code] is a text field that holds a two letter and three digit code, so something like 'AB001' or 'FH045', and [Name] is simply a text field containing the name of the file.

The objects on the form that hold them are actually TxtDataCode and TxtDataName.

Is this ok, or should I change the field names in the table to something else?

 

[JANR]

If the field in the table is called Code, then yes I would change it. If you'll be doing some VBA and must refer to this tablefield, you HAVE to use [] around it always or pequliar things will happen.

You can prefix it to strCode which isen't a reserved words.

JR