Need formula to auto-populate field

[knw]

Hi,

I am currently attempting to insert a formula either in the form or the corresponding table (whichever it is suppose to be) to automatically populate the title of the form/table (they are the same name) in that text box field to then have it be stored in the table with all of the other data.

If I insert an unbound textbox into my form I have tried to write in the Control Source field ="Classroom Outreach" this will then do what I want it to do in the form, by keeping all the fields on each record in that form set to "Classroom Outreach", but the problem is this data is not store in the record in the table.

I then tried putting the formula ="Classroom Outreach" in the Default Value and setting the Control Source to the field I would like it to go to in table called "Table Type". This does not work because "Classroom Outreach" is not automatically inserted in the field.

I hope this is clear, I will of course try and clarify. I really appreciate any guidance anyone can offer!!

 

[rainman89]

use a dlookup

 

[knw]

Thank you for telling me about Dlookup... I have tried 2 of the string formulas, are thoes the ones I should be using? here is the formula I inserted and it's not working:

DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")


DLookup("Table Name", "Classroom Outreach", "Criteria = '" & forms!Classroom Outreach!Classroom Outreach & "'")

I also used this one:

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

DLookup("Table Name" , "Classroom Outreach" , "Criteria= 'string'")

and

DLookup("Table Name" , "Classroom Outreach" , "Criteria= 'Classroom Outreach'")


Any suggestions?

 

[rainman89]

criteria means where something =something else... so you would do

dlookup("what you want returned", "fromwhere", "where this= that")

quick question... is this form bound to a query or to something else?

 

[Rabbie]

criteria means where something =something else... so you would do

dlookup("what you want returned", "fromwhere", "where this= that")

quick question... is this form bound to a query or to something else?

Just a small correction to Ray's advice. You don't put in 'where' in Dlookup so it should be
dlookup("what you want returned", "fromwhere", "this= that")

 

[rainman89]

Just a small correction to Ray's advice. You don't put in 'where' in Dlookup so it should be
dlookup("what you want returned", "fromwhere", "this= that")

right thats why its in quotes... didnt mean for the where to be inserted... guess i shoulda been alittle more clear on that one.

 

[knw]

Sorry I'm still confused....I inserted this

dlookup("what you want returned", "fromwhere", "this= that")

dlookup("Classroom Outreach", "Table Name", "Classroom Outreach= Classroom Outreach")

and it's still not working.

I have the field Table Name in my Classroom Outreach table that needs to be populated with the name of the Classroom Outreach form...so, Classroom Outreach would be the text that would poplate that field in the table. Do you know how I would set this Dlookup up with this information?

I am suppose to be inserted this formula in the Control Source field right?

To answer rainman I have this form bound to a table.....so the fields in the form when filled in the information is stored in the Classroom Outreach table.

Thank you for all your help here....this is probably simple, this stuff can be so confusing

 

[Rabbie]

Sorry I'm still confused....I inserted this

dlookup("what you want returned", "fromwhere", "this= that")

dlookup("Classroom Outreach", "Table Name", "Classroom Outreach= Classroom Outreach")

and it's still not working.

I have the field Table Name in my Classroom Outreach table that needs to be populated with the name of the Classroom Outreach form...so, Classroom Outreach would be the text that would poplate that field in the table. Do you know how I would set this Dlookup up with this information?

I am suppose to be inserted this formula in the Control Source field right?

To answer rainman I have this form bound to a table.....so the fields in the form when filled in the information is stored in the Classroom Outreach table.

Thank you for all your help here....this is probably simple, this stuff can be so confusing

Try something like

dlookup("Classroom Outreach", "Table Name", "[table Name].[Classroom Outreach] = Forms!formname![Classroom Outreach]")

 

[rainman89]

ok... so this is why you arent getting information with the dlookup.... its not there.

also it is better to be basing a form on a query than on the table itself...

couple quick questions...
how many tables do you have?
what are the exact field names in the table?
why is there a table name field? and how come that gets filled in?

can you post a sample database so we can take a look. this might be easier than having me ask you a bunch of questions

 

[knw]

Here is the sample database. I was trying avoid having to create a set of information in a table to have it lookup the information and insert it. I thought there would have just been a formula to have it reference the form name or something.

 

[rainman89]

we can have it do that.... but if the table name is classroom outreach, why do you have to store that in the table itself?
is there going to be more than one table?

 

[knw]

The reason I wanted to have the name of the table stored in the table is because when I run the report I have all the information from 6 tables coming together in a Union Query, so it is not specifying which table the data belongs too. So, I just wanted to be able to add that field.

 

[rainman89]

then it would be better to have a tablenames table and to it with a tablenames ID. easier to search on and also query

what we can do is set it to default to the value for a certain table

 

[knw]

Do you know how I would then have the field in the form reference the specific tablename ID if I made a table with different fields being the table names?

I guess I was trying to avoid making a combo box in the form - I just wanted it to know to insert it automatically.

 

[rainman89]

yeah we can set it up so it will show you the name give me a minute to work on this and ill post up a sample for you

ok see what i did.
1. created a table for table names
2. edited classroomoutreach table. added tableid
3. created query to base form classroomoutreach on that had both name and date.. hope this gives you an idea on where to go from here

 

[knw]

Rainman, thank you so much for all your help with this!!!!! You're a genius!

 

[rainman89]

haha no no far from it... glad to help

 

[rainman89]

also quick note.. when you set up your other tables... make sure to change the default value of the tableID field to the corresponding table name in the tablename Table.....

man that was a lot of table writing!