We need to clear up some loose ends here.
The 1st thing here is to ENSURE that the text box control name on the form
is DIFFERENT from that of the ANY field name of the forms underling
recordset. This is because you changing the source it points to, and ifon
forms-load time access will have trouble knowing the difference betweenthe
text box control and that of a field in the underlying table that is bound
to the form. In other words, reference the field name becomes confusing
because there also a control on the form with the SAME name, but not yet
bound!
Also make sure you have an option explicit at the beginning of this code
module? (if you don't, put it in).
eg at the start of your module have:
Option Compare Database
Option Explicit
Hum, why is ctrCource defined as a variant? This is simply string property.
I would define this as a string since that is the type of this property..
dim ctlSource as string
Hum, you not defined what data type ctl is. You should likely define this as
a data type = control
dim Ctl as control
However, looking at the code that follows, you likely don't need this Ctl
variable at all anyway.
Ctl = Form.Controls("ListPrice")
Ok, above looks ok, but I don't see you using Ctl anywhere else????
strCtl = Me!SISItemCode.Name
Is SISItemCode another control? The name property is going to ALWAYS bethe
same here. I really don't see the need for that line of code. I see you
stuff that "name" value into the table, but it is NEVER going to change
for the given code sample.
You might want to explain this use of "name".
ctlSource = ListPrice.ControlSource
As mentioned, the control source here is a STRING value. Make sure you
define ctlSource as a string then.
I see no place in your code where are you setting (or changing) the
ControlSource property of a text box on your form...
Perhaps this occurs somewhere else in your code not posted?
If you simply trying to change the control source of a text box on the form
them simply go:
me.txtListPrice.ControlSource = "string value of the underlying field you
want it be"
About the only suggestion here is that don't
name that text box the same as any field in the form as that's going
to confuse access.
Perhaps I miss understood your question, but I don't see where you are
setting the control source of any text box on your form.
Albert ...Thanks very much for sticking with this issue. I'm truly
hoping we can solve this. I will respond to your last post on a point
by point basis as follows
1) I have renamed my unbound control txtUnBound and there are no
others in my entire program
2) Option explicit was and stll is at the top
3) I now have Dim ctlSource as String
4) I now deleted reference to Ctl
5) SISitemCode is one of the bound controls on the form. dotName is
merely the Name of the control which is written to FieldName in the
tblMaterialMasterHistory
6) My code does compile as it now stands
7) I have debugged/Stepped thru all the way from the cmdClick to the
end without any error
Albert the issue is no longer the assigning the new control source but
is the errorr I get when I try to make the change to txtUnBound after
the form opens with the correct data. The error messagebox reads
"Function is not available in expression in table-level validation
expression". When the form opens on the first record and I try to
enter the first keystroke the above error message appears. Once I
dismiss the error message I can then make changes throughout the
entire recordset and the code runs correctly,
The following is the entire code from the cmdClick to OpenForm to
Loadform to TxtUnBound before update. I sure hope you can spot my
error. Thanks again for your assistance.
***************************************************************************************************************************************************
Private Sub cmdMMupdate_Click()
On Error GoTo cmdMMupdate_Click_Error
DoCmd.OpenForm "frmLPupdate", acNormal, , , acFormEdit, ,
OpenArgs:="Case1"
On Error GoTo 0
Exit Sub
cmdMMupdate_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure cmdMMupdate_Click of VBA Document Form_frmMainList"
End Sub
*************************************************************************************************************************************************************************
Private Sub Form_Open(Cancel As Integer) 'N.B. FORM:=frmLPupdate
On Error GoTo ErrorTrap
Me.Visible = False
'****************************************************************
Dim db As DAO.Database
Dim rs As DAO.Recordset
'****************************************************************
Dim strSQL As String 'String containing SQL for
recordset
Dim strItem As String 'Contains the new SQL
input SIS code String
Dim strMessage, strTitle As String 'Pertains to the InputBox
requesting the SIS code String for the SQL
'**************************************************************
Set db = CurrentDb()
strMessage = "Please input the SIS code filter string"
strTitle = "LIST PRICE UPDATE."
strItem = InputBox(strMessage, strTitle, Default, 5000, 3000)
If StrPtr(strItem) = 0 Then 'User clicked cancel
DoCmd.Close acForm, "frmLPupdate", acSaveNo
Exit Sub
End If
strSQL = "SELECT tblMaterialMaster.Funds,"
strSQL = strSQL & "tblMaterialMaster.SISItemCode,"
strSQL = strSQL & "tblMaterialMaster.CostPerInvUnit,"
strSQL = strSQL & "tblMaterialMaster.Supplier,"
strSQL = strSQL & "tblMaterialMaster.Contents,"
strSQL = strSQL & "tblMaterialMaster.ManufacturerName,"
strSQL = strSQL & "tblMaterialMaster.LocalGroup,"
strSQL = strSQL & "tblMaterialMaster.LocalSubGroup,"
strSQL = strSQL & "tblMaterialMaster.ManufacturerNo,"
strSQL = strSQL & "tblMaterialMaster.MaterialDescription,"
strSQL = strSQL & "tblMaterialMaster.MaterialNote,"
strSQL = strSQL & "tblMaterialMaster.CorpMatlGrp,"
strSQL = strSQL & "tblMaterialMaster.InvUnit,"
strSQL = strSQL & "tblMaterialMaster.ListPrice,"
strSQL = strSQL & "tblMaterialMaster.Discount,"
strSQL = strSQL & "tblMaterialMaster.CostDateNote"
strSQL = strSQL & " FROM tblMaterialMaster"
strSQL = strSQL & " WHERE ((tblMaterialMaster.SISItemCode) Like"
& """" & strItem & """)"
strSQL = strSQL & " ORDER BY tblMaterialMaster.SISitemCode"
strSQL = strSQL & " "
Set rs = CurrentDb.OpenRecordset(strSQL)
Me.RecordSource = strSQL
Me.Visible = True
'Me.txtUnBound.SetFocus
NormalExit:
Exit Sub
ErrorTrap:
If Err = 2501 Then
Resume Next
Else
MsgBox Err.Description, , Str(Err)
Resume NormalExit
End If
End Sub
******************************************************************************************************************************************************
Private Sub Form_Load()
Dim strNewSrce As String
If IsNull(Me.OpenArgs) = False Then
Select Case Me.OpenArgs
Case "Case1"
strNewSrce = "ListPrice"
Me!Label3.Caption = "Current List"
Me.Caption = "List Price Update"
'Me.ListPrice.ControlSource = strNewSrce
'Form![ListPrice].ControlSource = strNewSrce
Me.Controls("txtUnBound").ControlSource = strNewSrce
Case "Case2"
' Does nothing yet
End Select
End If
End Sub
*****************************************************************************************************************************************************
Private Sub txtUnBound_BeforeUpdate(Cancel As Integer)
Dim ctlSource As String, intCtl As Integer
'Dim Ctl As Control
Dim strCtl As String
Dim db As DAO.Database
Dim rsMMhist As DAO.Recordset
'*******************************************************
On Error GoTo txtUnBound_BeforeUpdate_Error
Set db = CurrentDb()
Set rsMMhist = db.OpenRecordset("tblMaterialMasterHistory")
'Ctl = Form.Controls("ListPrice")
strCtl = Me!SISItemCode.Name
ctlSource = txtUnBound.ControlSource
rsMMhist.AddNew
rsMMhist!FieldName = strCtl
rsMMhist!UserName = CurrentUser()
rsMMhist!SISItemCode = SISItemCode
rsMMhist!ChngeDate = Now()
rsMMhist!OldListPrice = txtUnBound.OldValue
rsMMhist!NewListPrice = txtUnBound.Value
rsMMhist!ControlSource = ctlSource
rsMMhist.Update
On Error GoTo 0
Exit Sub
txtUnBound_BeforeUpdate_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure ListPrice_BeforeUpdate of VBA Document Form_frmLPupdate"
End Sub
*******************************************************************- Hidequoted text -
- Show quoted text -
