Input form data into table

[p4nny1984]

Hi,

By using an add record button, I would like to add the values on a form (tbfullname) to the table (L1 Reviwer), table is called LC1_Tbl

Some fields on the form have expressions within.

Please could you help whether it be by VBA or SQL?

Form name is called Lc1_tbl
Field in table is L1 Reviewer
textbox on form is called tbfullname

Your help is greatly appreciated.

[Seth Schrock]

Is there a reason that you don't want the form bound to the table, which would make it so that no code is necessary to enter the data from the form into the table?

[p4nny1984]

Thanks for the reply..

I have expressions attached to some 2 of the text boxes so it doesn't seem possible to have these bound to the table.

One expression uses the fOSusername() function and the other is the date

[Seth Schrock]

Put the expressions in the Default value property instead of the control source property.

[p4nny1984]

Just tried to enter this:

="Welcome, " & DLookUp("[Analyst Name]","Userlist","[LAN ID]=userlanid")

as the default value.. When go to to form view. The text box is empty where as it works ok in the control source.

Cheers

[Seth Schrock]

Are you looking for the analyst's name whose LAN ID is UserLanID or whose LAN ID is in the UserLanID control? If the latter, then you need to remove it from within the double quotes and also need include single quotes around the value if the value is text.

[p4nny1984]

I have a table called userlist which shows LAN ID and Analyst Name

Using the fOSusername()function, this returns the LAN ID. The dlookup then returns the analyst name based on the userlist..

I think it is the first scenario you present

thanks for help with this.

[Seth Schrock]

It would actually be the second one because no ones LAN ID is "UserLanID". Mine would be "sschrock" for example. Try the following:

="Welcome, " & DLookUp("[Analyst Name]","Userlist","[LAN ID]='" & fOSUserName() & "'")

[p4nny1984]

UserLanID is just the name of a the textbox on the form.

Don't think this one is going to be resolved. I've been trying to find a solution for about 2 weeks.

What about an insert into statement

[Seth Schrock]

You can do it that way, but then you also have to read from it via code to populate your controls.

I just did some testing, and evidently the DLookup() function doesn't work too well in the Default Value property. Try adding your code to the form's On_Load event to assign the value to your textbox.

Me.Textbox_Name ="Welcome, " & DLookUp("[Analyst Name]","Userlist","[LAN ID]='" & fOSUserName() & "'") 

[p4nny1984]

Great works perfect with my dlookup..userlanid is text that returns returns the fosuername

One very last thing I promise... When I click Add Record the record fires to the tables! I would like this particular textbox to refresh and show the value again ready for the next record.

Within Form properties, in event, I don't know which one to choose.. Any idea?

Many thanks for your help today

[Seth Schrock]

Switch it to the form's OnCurrent event and it will change for each record.

[p4nny1984]

Appreciate your guidance here. Works great. Thank you!

[Seth Schrock]

Glad to help. Good luck on your project.