populating a tbl field from an unbound control

[kkpen]

I have a popup dialog box that ask for a date, once the date is entered a form opens up with the date just entered in an unbound box(DrillDate). The form is based on a tbl which has the name, section, and times that a person was present for work. The form is a contiuous form, and the size varies on which section is selected via a drop down box. I would like the date that has been copied to the unbound box to populate the date field in the table(stDrillDate) for every record that was updated. Right now it is in the after insert event with the code of:
------
Me!stDrillDate = Me!DrillDate
------

After the first record is updated i get an runtime 7878, the data has been changed.

is the code in the right event?

Thanks

 

[Rob.Mills]

It would probably be best to use an Update query in this case.

 

[kkpen]

where the form is based on a qry do i make that qry and update qry or make a new qry for the update part? also would i put in the stDrillDate criteria: =forms!frmAttenPay!DrillDate -- which is the unbound field with the date i want

 

[Rob.Mills]

You'll have to make a separate query for the update as you can't use an update query as a recordsource.

Then you'll only need to add the fields you're going to update and any fields that have criteria to limit the records.

Where it says Update To: type 'Forms!FormName!UnboundTextbox

Check in the help section under query >> Update query for guidance.

 

[kkpen]

Thanks Rob!

I have created the update query but when i put it in the afterupdate for the combo box (i have to use the combo box to make the first selection) i get a cant use find or update now error.

is there a way to have the popup the opens the form to select the section?

 

[Rob.Mills]

I'm a little confused. You've got a popup box with a combo in it. You select a date from the combo and then open another form. right? Post me your code.

 

[kkpen]

The date is in a popup dialog box, i input the date and then click on a open form button.

heres the code for the popup open form button:
--------------------------
Private Sub Open_Form_Click()
On Error GoTo Err_Open_Form_Click

Dim stDocName As String
Dim stCriteria As String
Dim indate As Integer


If IsNull(Me![StDrillDate]) Then
MsgBox "You did not enter a Start Drill Date. Enter one and try again.", vbOKOnly, "STARTING DRILL DATE ERROR"
DoCmd.GoToControl "stDrillDate"
Exit Sub

End If

'Test code to check for day of week
indate = Weekday(StDrillDate)

If indate = 6 Then
'MsgBox "Its Friday", vbOKOnly

ElseIf indate = 7 Then
'MsgBox "its saturday", vbOKOnly


ElseIf indate = 1 Then
'MsgBox "its sunday", vbOKOnly

Else

MsgBox "You must enter a Friday, Saturday, or Sunday Date", vbOKOnly, "ENTER A FRI, SAT, OR SUN DATE ERROR"
DoCmd.GoToControl "stDrillDate"
Exit Sub


End If

stDocName = "frmAttenPay"
DoCmd.OpenForm stDocName, , , , acFormEdit

Exit_Open_Form_Click:
Exit Sub

Err_Open_Form_Click:
MsgBox Err.Description
Resume Exit_Open_Form_Click

End Sub
----------------------


then the form opens with the following code in on_load:


-----------------------
Private Sub Form_Load()
Dim indate

indate = Weekday(Forms!dbxStDrillDate!StDrillDate)
If indate <> 6 Then
'MsgBox "fri show", vbOKOnly
'MsgBox "fri hide", vbOKOnly
Me![cbxFri1].Visible = False
Me![cbxFri2].Visible = False
Me!labFriday.Visible = False
Me!labPD5.Visible = False
Me!labPD6.Visible = False


End If

DoCmd.Close acForm, "dbxStDrillDate"
'DoCmd.Requery (Me!SECTION = "hq plt")
'DoCmd.GoToControl "Forms!frmAttenPay!cbxFindSection"
'"[section]='hq plt'"


End Sub
--------------------------------

The combo box lists the different sections and I would like it to default to Hq Plt, I think if i could get the form to open with a section already displayed, then the afterupdate with the update query would probably work when i selected another section or closed the form.

Again Thanks for all your help. (This is a diff. db than my other post, it could be sent to you if that would help, would have to send in the morning though, the most current copie is at work)

 

[Rob.Mills]

Sending it to me might help. I'm wondering, though if you can eliminate a step. You've got the query pulling the date from 2nd form. But the 2nd form pulls it from the popup form. Why have the middleman? I've setup many situations where I set a value in a popup form and then hide it. Then open another form pulling from the hidden form.

But go ahead and send it to me. I'll take a look at it.