Populating Unbound Objects

[CanWest]

Ok. This is a little complex so I hope that I explain myself correctly.

I use a customized log in system that I found on these forums. Iw works flawlessy.

When a the database is opened frm_Logon opens.
In the on open event of that form frm_LogonStorage is opened. This is a hidden form.

On frm_Logon there is a combo box control called cboEmployee that gets it's data from a tacle called tbl_User

On this control's afterupdate event I have the following code

Private Sub cboEmployee_AfterUpdate()
'After selecting user name set the values in the hidden form frm_LogonStorage
    Forms!frm_LogonStorage!UserName = Forms!frm_Logon!cboEmployee.Column(1)
    Forms!frm_LogonStorage!UserID = Forms!frm_Logon!cboEmployee.Column(0)
    Forms!frm_LogonStorage!SecurityLevel = Forms!frm_Logon!cboEmployee.Column(2)
    Forms!frm_LogonStorage!UserFirstName = Forms!frm_Logon!cboEmployee.Column(3)
    Forms!frm_LogonStorage!UserLastName = Forms!frm_Logon!cboEmployee.Column(4)
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub

The purpose of storing this data in a hidden form is to control who can edit records on another from called pfrm_EditContactNotes

The onopen event of this for has the following code

    If Forms!frm_LogonStorage!UserID = Me.ChangeID Then
        Me.cmdSave.Visible = True
        Me.cmdDelete.Visible = True
        Me.Subject.Locked = False
        Me.ContactNote.Locked = False
        Me.cmdCancel.Caption = "Cancel Change"
        
    Else
        Me.cmdSave.Visible = False
        Me.cmdDelete.Visible = False
        Me.Subject.Locked = True
        Me.ContactNote.Locked = True
        Me.cmdCancel.Caption = "Close Form"
        
        MsgBox "You did not create this contact note, therefore you do not have permission to change it or delete it. However you can view it and copy it's contents.", vbApplicationModal + vbExclamation, "Security Warning"
    End If
    

End Sub

Now here is the therory bedhind this. When pfrm_EditContactNote opens it checks to see if the logged on user (UserID in frm_LogonStorage) is the user that created the record (ChangeId on pfrm_EditContactNote)

If I allow the vba to fill in the fields in frmLogonStorage, when pfrm_EditContactNote opens it always use the else part of the If statement. However if I manualy type the UserID in frm_LogonStorage the form pfrm_EditContactNote opens correctly depending who is logged on.

I am feeling this has someting to do with the number format but have tried making sure they are the same format to no avail.

Any assistance with this would be greatly appreciated.

 

[RuralGuy]

When the pfrm_EditContactNotes form opens is there a valid ChangeID yet?

 

[CanWest]

When the pfrm_EditContactNotes form opens is there a valid ChangeID yet?

Yes. That field is populated when the record is created

 

[RuralGuy]

But is the record created before the for opens?

 

[CanWest]

But is the record created before the for opens?

Yes the form is opening a previously created record

 

[RuralGuy]

Hmm...as a diagnostic, put a MsgBox in that code to display the two fields and see what the program sees. You could single step as well.

 

[CanWest]

Hmm...as a diagnostic, put a MsgBox in that code to display the two fields and see what the program sees. You could single step as well.

I am self and forum taught in vba and I have no idea how to do that. Would you be so kind as to enlighten me??

 

[RuralGuy]

Sorry but I've been in a meeting all ofn this time. In the vb editor, click to the left of the margin on the IF line and a dot will appear. That is a breakpoint. Run the form and the code will stop on that line and open the editor at that line. Hover over the variables and the values will show in a pop-up balloon.

 

[CanWest]

Sorry but I've been in a meeting all ofn this time. In the vb editor, click to the left of the margin on the IF line and a dot will appear. That is a breakpoint. Run the form and the code will stop on that line and open the editor at that line. Hover over the variables and the values will show in a pop-up balloon.

Ok now I know what is going on. Thanks for that tip it showed me that the following code is entering the UserID in the frm_LogonStorage as a text value rather than a number. The value in frm_Logon in control cboEmployee.Column(0) is in fact numeric. It is the primary key of the underlying table tbl_User

 Forms!frm_LogonStorage!UserID = Forms!frm_Logon!cboEmployee.Column(0)

Is there a way to force it to be a numeric value rather than text

 

[RuralGuy]

Well you have to separate the display and the storage of values. You can store values in a table or variable as either text or numeric. Displaying them however is text. The values "displayed" in a ComboBox are always text even though the field they are displaying may be numeric. Is the UserID control in the frm_LogonStorage form bound (has a ControlSource of) to a field in a table? If not, then what you are asking will not work.
A better approach would be to change this line:
If Val(Forms!frm_LogonStorage!UserID) = Me.ChangeID Then

 

[CanWest]

Well you have to separate the display and the storage of values. You can store values in a table or variable as either text or numeric. Displaying them however is text. The values "displayed" in a ComboBox are always text even though the field they are displaying may be numeric. Is the UserID control in the frm_LogonStorage form bound (has a ControlSource of) to a field in a table? If not, then what you are asking will not work.
A better approach would be to change this line:
If Val(Forms!frm_LogonStorage!UserID) = Me.ChangeID Then

Thanks I will try that at a later date. I really appreciate your help. I was able to resolve the issue buy using a different field (UserName) which is unique to each logged on user. These validates exactly what you are saying because this is a text field and it worked perfectly.

 

[RuralGuy]

Thanks for posting back with your success and solution.