Refreshed form loses combo box link

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I have a combo box on a popup form that is linked to a
text box; however, when I close the form and reopen it I
lose the link between the combo and text boxes. (this is
the only way I've found to refresh the form - I tried
me.refresh and requery but neither works) Also, I'm not
using a Macro because I want the form to reopen to a
specific record.
What I am trying to do is have the user select a person
from the combo box and press a command button to change
the person's status. The form is then refreshed to
reflect this change in status. When the form reopens, I
would like the user to be able to select another person in
the combo box if they need to and change their status as
well. However, the combo box loses its link with the text
box. Here is my code linking the the combo box:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NUMBER] = " & Str(Me![OffNam])
Me.Bookmark = rs.Bookmark

Here is my code under one of the Status updating buttons:
Dim Onum As Integer
Dim stDocName As String

Onum = Me.NUMBER
DoCmd.RunSQL "UPDATE Staff set Staff.Status = False WHERE
Staff.Number = " & Onum & "" 'updates status in record
DoCmd.RunCommand acCmdSaveRecord

stDocName = "STATUS UPDATE"
DoCmd.Close acForm, stDocName
'open form to the updated record
DoCmd.OpenForm "STATUS UPDATE", , , "NUMBER = " & Onum & ""

It all works fine but the combo box loses its link to the
text box even though the form is being reopened.

TIA.....Al
 
Al,
What I am trying to do is have the user select a person
from the combo box and press a command button to change
the person's status. The form is then refreshed to
reflect this change in status. When the form reopens, I
would like the user to be able to select another person in
the combo box if they need to and change their status as
well. However, the combo box loses its link with the text
box. Here is my code linking the the combo box:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NUMBER] = " & Str(Me![OffNam])
Me.Bookmark = rs.Bookmark
Click to expand...

This code seems OK, however make sure the combo is unbound. I don't
quite understand what do you mean by 'losing the link to the text
box', but I have a feeling the combo could be bound.
Here is my code under one of the Status updating buttons:
Dim Onum As Integer
Dim stDocName As String

Onum = Me.NUMBER
DoCmd.RunSQL "UPDATE Staff set Staff.Status = False WHERE
Staff.Number = " & Onum & "" 'updates status in record
DoCmd.RunCommand acCmdSaveRecord
Click to expand...

This is not quite clear. First, you update a record in the table, then
you save the record from the form, writing whatever changes in the
table. Why this?

If the Status is visible on the form (I guess that's why you must
refresh it), then why not simply assign the value False to the form
control and save the record in the button's Click procedure? Like
this:

Me!Status = False
DoCmd.RunCommand acCmdSaveRecord

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Back
Top