Form not saving one field to its table ?

[Jgk]

I have a form named frmHours
it opens a table named hours to new record
table called Hours
field in table is called Contractor (number field lookup) combo

on Form I changed from combobox to text and it enters info from form to detail section- set to continuous form
by:
control source set to for form =[Forms]![frmHours]![txtContractor]

Here is Code for Form:

Option Compare Database
Option Explicit
Private Sub Command5_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub Form_Load()
Dim iEdit As Integer

'populate the text boxes
Me.txtName = Environ("username")
Me.txtDate = Date
Me.txtContractor = DLookup("Contractor", "Contractors", "Login='" & Forms!FrmMenu!txtUser & "'")
'set edit permissions
iEdit = Forms!FrmMenu!txtLevel.Value
Select Case iEdit
Case Is < 2
Me.AllowEdits = False
Case Else
Me.AllowEdits = True
End Select
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)



ModBy = Environ("username")
ModDate = Now()
End Sub

However, it is saving all info to Hours table but not name(contractor) field, what step am I missing

Thanks,

[ItsMe]

I am not sure I understand perfectly but you should note that unbound controls do not always behave in a predictable way when used in continuous forms or datasheet view.

[Jgk]

It is a new record(Continuous form) and it updates the form with the field from the unbound text box, but after saving record, the data does not flow to table.

[ItsMe]

Before executing code like this

Me.txtName = Environ("username")
Me.txtDate = Date

in a continuous form, you need to select/set focus on a record. If you click a button, you will not have focus on a record and Access will not know which record to update.

[Jgk]

it is updating and adding a record just fine except not the field contractor is left blank in table, I thought there is a control source field issue.

[ItsMe]

What happens of you try this then?

Me.txtContractor = "Test Answer"
Debug.print DLookup("Contractor", "Contractors", "Login='" & Forms!FrmMenu!txtUser & "'")

And check the Immediate Window to see if your DLookup is working

[Jgk]

test answer is entered in field contractor in immediate window it shows actual contractor name

[Jgk]

test answer in unbound text field and entered in field on continuous form, but in immediate window it show correct contractor name

[ItsMe]

So the text, "Text Answer" displays in the control but not the table? And the DLookup expression works good because what is in the Immediate window is correct?

So, yes, there is an issue where the control on the form is not communicating with the table. You mention that the control is Unbound. You can bind a control to a field in a table by adjusting the control's "Control Source" property.

Unbound controls do not always behave in a predictable way when used in continuous forms or datasheet view.

[Jgk]

Yes that is what I am trying to figure out how to do,set the control source for the unbound control on main form

[June7]

If you are populating unbound textboxes on Continuous form, every record will reflect the same values.

If you have the user ID, why do you need to also save the contractor name? If user is associated with specific contractor, the contractor can always be retrieved in a query that joins tables. Saving the contractor name is duplication.

[ItsMe]

Very little of your code in post #1 makes sense to me. When I consider your comments about what your code is supposed to be doing and how your forms function, the whole thing makes even less sense.

If you want to get data to the table, data that is available via Environ() and other built in functions like Date(), then assign these variables to a bound control. If the Main form is not bound and you do not have the ability to bind a control to a field in a form’s recordset; the other options are

DAO
or
Action query like UPDATE

You bind a control by going to the control's properties. You can see the "Control Source" property in the property sheet, under the "Data" tab. Selecting a control Source will bind the control to that field.

In order to assign a variable to a control that is bound to a field, use VBA.
Me.ControlName.Value = Environ("username")

[Jgk]

I cannot get to work, only one form then I will be up and running. I have attached a sample db of what is going on. Any help would be greatly appreciated too many hours spent trying to get to work.

[ItsMe]

You have a continuous form with unbound controls in the header. The unbound controls are, well, unbound. They don't know what is going on with your continuous form. As you enter records in a continuous form, there are new rows/records with new PK values to uniquely identify said records. An unbound control is not going to know how many, how few, or if any records are created. It certainly is not going to know what the PK value is of a given record so, it will not be able to update a record (there is always a way around something to get something done but not going to explain how here).

Maybe you can get what you need with using the Afterupdate of a control bound to a required field. Maybe CboQBCODE would be a good candidate. Then write some code to populate a control (in your Continuous form) that is bound to a field (drag a control from the "Add Existing Fields List" to your Detail Section)

Maybe some code like

Me.FieldForUserName.Value = Environ("username")

In the after update of CboQBCODE

AS for the other fields... maybe a default value of Date() for the date field. No code required. Just put the date function or Now() function in the default value at the table level, while in design view of the table. A new record gets created and, whamo, the date magicaly appears.

The contractor field. Seems like you got contractor fields or unbound controls named “Contractor” all over the place (try normalization for starters).

[June7]

You have two controls on the form that deal with the contractor - one in the header (txtContractor) and one in the detail (Contractor).

Options:

1. bind Contractor textbox to the field then use code to populate the textbox or the field
Me.Contractor = Me.txtContractor
or
Me!Contractor = Me.txtContractor

2. expression in the Contractor textbox: =[txtContractor] and still use code to populate the field
Me!Contractor = Me.Contractor
or
Me!Contractor = Me.txtContractor