Obtaining a value in a form from a table

[Cedarguy]

I need to get a value from a table on to a form based on a value that exists on the form. The field being sought is part of the record being assembled that would be appended to a table. The only way I think I can get it is via an expression in the default value (properties) but have not succeeded. I have tried to use dlookup and select but the field starts out with #name? and then nothing.



Is default value the right place to do this? I'm thinking not.

Would appreciate some guidance.

Thanks

[June7]

I just tested DLookup expression as a DefaultValue and it did work. Show your attempted code.

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[Cedarguy]

Thank you June7,
Having trouble with compact & repair - it said the DB is in use; any suggestions?
Meanwhile, my expression is: =DLookUp([Tutor]![Tut-Rate],[Tutor],[Tutor]![ID]=[Ses-Tutor ID])
Note: Ses-Tutor ID is populated on the row being created, from a combobox which displays the names and IDs for tutors; naturally, the value would not be there when the screen is brought up; don't know if that a problem.
Thanks again.

[June7]

Arguments must be within quotes unless they are variables and variables must be concatenated. Brackets [] needed if name contains spaces, special characters or are reserved words.

=DLookUp("Tut-Rate","Tutor","ID=" & [Ses-Tutor ID])

And yes, if the Ses-Tutor ID value is not populated the default won't work. You will have to grab Tut-Rate through some other method. Options:
1. table join
2. multi-column combobox
3. DLookup in event code

Are you the only user of the db? With the file closed can you delete the ldb file? If the file is not so big without Compact & Repair, try zipping and attaching it anyway.

[Cedarguy]

Thanks for your helpful suggestions and comments, June7.
I'll prep the DB for sending as an attachment next time I need to do so.
I'll report back after trying your suggestions.
Yes, I am the only user of the database and I'm porting my excel based small business data over to access, as excel can no longer handle the volume efficiently.
Regards

[June7]

Okay, be aware none of the methods presented will actually save the value to the table. That will require code in some event.

[Cedarguy]

Thanks; that would mean learning something called VBA, right? LOL
Would you be kind enough to refer me to a good VBA reference? I do have a distant programming background in procedural languages with only theoretical OO knowledge.
With some practice I'm sure I'll pick up enough VBA to serve my proposes.
Many thanks.

[June7]

Or macros but I don't use macros unless absolutely have to.

Start with http://www.bing.com/search?q=access+...t&sp=1&sc=8-10

I've never used a VBA specific reference, some VB6 and VB.net references and basic intro to Access.

However, this one looks good
Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc.

If the field you want to save value to is included in the form's RecordSource, code could be simply like:

Me!yourfieldname = Me.yourtextboxname

The trick is figuring out what event to put the code in.

[Cedarguy]

Thank you; I'll give your suggestions a shot later today; regards