How to create a lookup form

[Colin Coleman]

I am new to Access 2007, i come from a Delphi background and am trying to create a master form that contains employment details, i have created 10 other tables like paydetails, holidaydetails, healthdetails, Etc....

I want to be able to load into the master table field for say Paydetails the paydetails from the paydetails table, depending on a selection made in a selection/popup form.

i am able to create a form but am stuck as to the best way to code returning the memo data from the popup form to the calling master form. i think i need a method on the form that the master can call that makes the popup show its self. then either does some form of callback function (delphi/windows) or stuff the result into a global var on the master form.

Am i on the right lines ?.... and can anyone point me to an example.

Thanks if you can help....

 

[dkinley]

See posted example if you are looking for other forms to update an underlying form.

With this method, there is alot of data redundancy. Might want to think about just linking the primary key ID fields and calling the linked information on an as-needed basis.

Just a very dry example - anyhow, not sure if this is what you wanted.

-dK

 

[Colin Coleman]

Hi thanks for the rply,
I usually optimise a database to death, but the customer needs to gaurantee that when the master lookup tables are changed it DOES NOT update the contract information that was stored for that particular employee in his contract master record. Even at the expense of making a very FAT and wide employee contract record.

When a new contract is issued, it will select the type of job and go get the current details from the lookup tables and effectivly LOCK them into the master contract record for that employee.

I have created a form with the tabs for say sick, health,pension,holiday,Etc... and on each one added a button to launch a lookup form, this then returns the memo text from the lookup and stores it into the master record. ....i hope

i created a public var in a module last night and it seems this is access able in each form so this maybe a solution..... is this a good idea ?

Best regards
Colin

 

[dkinley]

Understood .. copying the data in this scenario makes perfect sense. For certain fields I definitely do the same (pricing data for models and even model number info) as they change over time. So "locking" them in is the only way to achieve objectives.

The method I shown will work as well as a public variable. Like somone's tag here says -- there more than one way to skin a cat. I would also take a look at the form properties 'modal' and 'pop-up' to ensure the look-up form does not accidently go behind the tabbed form if they off click the form.

The only concern I have is you keep using the word "memo". Memo fields are known to be a source that could cause data corruption. I forget why off the top of my head so I trend to use only text fields (limit of 255 characters).

-dK

 

[Colin Coleman]

Humm slight problem with the good 'ol text fields.... i am using the text/memo data to stuff into a word document mail merge, and some of the sub section, para,para,para..blah blah legal looking sections are large-ish, like 2-3 k and i have also found that if i use the memo it seems to be able to do the autonumbering correctly.

If you have any other ideas though id be interested, because as with delphi the best way to learn is from others mistakes....

Thanks so much for the help already

 

[dkinley]

I think with anything is to build upon those mistakes of anyone ...

I found this article relating '97 and memo fields by doing a quick google - http://support.microsoft.com/kb/235391

Maybe it's just a stigma I still carry with me.

I don't think there would be an issue using memo fields to hold static data for mail merging. That is, if each output contains the same legal info for instance.

Another method would be to pre-format your reports with the static info and then slide in text controls for like the names and dates. I have done this for legal documents and certificates for various databases. Another method I have used is a pre-formated Excel spreadsheet and populated that based on data I have queried out of the database.

The only drawback to Access, IMHO, is that it spits info out into an incompatible report (and .rtf strips away cosmetic formatting). Other than using templates or mail merges, I have printed to .pdf to overcome this obstacle.

I am not sure what you mean by saying only the memo fields are doing the autonumbering correctly?

-dK

 

[Colin Coleman]

Thanks Looks like its working ok now......
i have another strange problem but i will make a new post


Thanks to Dk