Control afterupdate - run from module - insert data - set flag

  • Thread starter Thread starter Billp
  • Start date Start date
B

Billp

Hi,
Can I do what I am hoping I can do with running a routine from a module on
the after update of a combo box.

What I am trying to do is catch if a new entry is there, and then not allow
the form to be saved (latter issue), and if the entry is part of the existing
database continue to populate data form that table to the forms controls.

The afterupdate is as follows
Call SER_Update ' from module

Me.Refresh


Then to the module
Sub SER_Update()

'user has entered a customer from the combo
'check if it is already in the customer database
'if it isn't then stop the enquiry form being saved
'by setting a flag
'until the new customer is added to the db and everything is updated

Dim rst As DAO.Recordset 'rsttblCustomers
Dim strsql As String
Dim strNewCust As String
Dim User_form As Access.Form 'users form

strNewCust = User_form!frmSER!cboCompany_Name

Set rst = CurrentDb().OpenRecordset("tblCustomers", dbOpenDynaset)
strsql = strNewCust
rst.FindFirst strsql

If rst.NoMatch Then 'the input is not in the list
' do something like set the save form to no until customer is added

User_form!frmSER!FormSaved = True 'sets the flag
'set up a messgage to notofy user that form cannot be saved

Else 'there is a match and customer is already in database

'populate fields with existinmg data

'by selecting the row you have effectually said start
Dim strFilter As String

' Evaluate filter before it is passed to Dlookup function.

strFilter = "[Customer_Record_ID] = " &
User_form!frmSER![cboCompany_Name].Column(1) 'as it is a numeric value

'Update form controls based on value selected in cboCompanyName combo box

User_form!frmSER![Customer_Phone] = Nz(DLookup("[Customer_Phone]",
"tblCustomers", strFilter))
User_form!frmSER![IndustryCode] = Nz(DLookup("[CustomerSIC]",
"tblCustomers", strFilter))
'User_form!frmSER![Company_Name] = Nz(DLookup("[Company_Name]",
"tblCustomers", strFilter))
User_form!frmSER![CustomerID] = Nz(DLookup("[CustomerID]",
"tblCustomers", strFilter))
User_form!frmSER![Address] = Nz(DLookup("[Address]", "tblCustomers",
strFilter))
User_form!frmSER![Address1] = Nz(DLookup("[Address1]", "tblCustomers",
strFilter))
User_form!frmSER![Address2] = Nz(DLookup("[Address2]", "tblCustomers",
strFilter))
User_form!frmSER![Address3] = Nz(DLookup("[Address3]", "tblCustomers",
strFilter))
User_form!frmSER![Address4] = Nz(DLookup("[Address4]", "tblCustomers",
strFilter))
User_form!frmSER![Address5] = Nz(DLookup("[Address5]", "tblCustomers",
strFilter))
User_form!frmSER![Address6] = Nz(DLookup("[Address6]", "tblCustomers",
strFilter))

End If
'return to form
End Sub

What I am getting is an error 91 with a variable not set????????? on the
cboafterupdate.

Help please - any help appreciated
Thanks
Bill
 
Hi,
at what line you are getting this error?

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Billp said:
Hi,
Can I do what I am hoping I can do with running a routine from a module on
the after update of a combo box.

What I am trying to do is catch if a new entry is there, and then not
allow
the form to be saved (latter issue), and if the entry is part of the
existing
database continue to populate data form that table to the forms controls.

The afterupdate is as follows
Call SER_Update ' from module

Me.Refresh


Then to the module
Sub SER_Update()

'user has entered a customer from the combo
'check if it is already in the customer database
'if it isn't then stop the enquiry form being saved
'by setting a flag
'until the new customer is added to the db and everything is updated

Dim rst As DAO.Recordset 'rsttblCustomers
Dim strsql As String
Dim strNewCust As String
Dim User_form As Access.Form 'users form

strNewCust = User_form!frmSER!cboCompany_Name

Set rst = CurrentDb().OpenRecordset("tblCustomers", dbOpenDynaset)
strsql = strNewCust
rst.FindFirst strsql

If rst.NoMatch Then 'the input is not in the list
' do something like set the save form to no until customer is added

User_form!frmSER!FormSaved = True 'sets the flag
'set up a messgage to notofy user that form cannot be saved

Else 'there is a match and customer is already in database

'populate fields with existinmg data

'by selecting the row you have effectually said start
Dim strFilter As String

' Evaluate filter before it is passed to Dlookup function.

strFilter = "[Customer_Record_ID] = " &
User_form!frmSER![cboCompany_Name].Column(1) 'as it is a numeric value

'Update form controls based on value selected in cboCompanyName combo
box

User_form!frmSER![Customer_Phone] = Nz(DLookup("[Customer_Phone]",
"tblCustomers", strFilter))
User_form!frmSER![IndustryCode] = Nz(DLookup("[CustomerSIC]",
"tblCustomers", strFilter))
'User_form!frmSER![Company_Name] = Nz(DLookup("[Company_Name]",
"tblCustomers", strFilter))
User_form!frmSER![CustomerID] = Nz(DLookup("[CustomerID]",
"tblCustomers", strFilter))
User_form!frmSER![Address] = Nz(DLookup("[Address]", "tblCustomers",
strFilter))
User_form!frmSER![Address1] = Nz(DLookup("[Address1]", "tblCustomers",
strFilter))
User_form!frmSER![Address2] = Nz(DLookup("[Address2]", "tblCustomers",
strFilter))
User_form!frmSER![Address3] = Nz(DLookup("[Address3]", "tblCustomers",
strFilter))
User_form!frmSER![Address4] = Nz(DLookup("[Address4]", "tblCustomers",
strFilter))
User_form!frmSER![Address5] = Nz(DLookup("[Address5]", "tblCustomers",
strFilter))
User_form!frmSER![Address6] = Nz(DLookup("[Address6]", "tblCustomers",
strFilter))

End If
'return to form
End Sub

What I am getting is an error 91 with a variable not set????????? on the
cboafterupdate.

Help please - any help appreciated
Thanks
Bill
Click to expand...
 
The error occured at the "Call" so it didn't seem to get to the routine.
Having said that
The error trapping didn't originally stop at a line untill I modified it.

The basis behind the question I have is from a brief.

"If the customer does not exist from the dropdown then the user will be
allowed to type in the customer name and other details as disussed by the
client. If the customer exists then the user will be allowed to enter a
contact name and the details will automatically fill. This is nexessary as we
do not want to stifle the conversation flow with the client."

With the suggestion from the reply from Jim Burke I can get into the routine,

"I think your User_form variable is your problem.You've declared that as a
form but never set it, and it's not needed anyway. I don't know that that's
even a valid way to refer to Forms. To reference controls on a form you
should just use

Forms!frmName!ctlName

The updated code is

Function SER_Update()

'user has entered a customer from the combo
'check if it is already in the customer database
'if it isn't then stop the enquiry form being saved
'by setting a flag
'until the new customer is added to the db and everything is updated

Dim db As DAO.Database
Dim rst As DAO.Recordset 'rsttblCustomers
Dim strsql As String
Dim strNewCust As String

strNewCust = [Forms]![frmSER]![cboCompany_Name]

MsgBox "here in the module"

Set db = CurrentDb
Set rst = db.OpenRecordset("tblCustomers", dbOpenDynaset)
strsql = strNewCust
rst.FindFirst strsql

If rst.NoMatch Then 'the input is not in the list
' do something like set the save form to no until customer is added

[Forms]![frmSER]![FormSaved] = True 'sets the flag
'set up a messgage to notify user that form cannot be saved
MsgBox "This Enquiry Cannot be saved untill you ADD the new customer."

Else 'there is a match and customer is already in database

'populate fields with existing data

'by selecting the row you have effectually said start
Dim strFilter As String

' Evaluate filter before it is passed to Dlookup function.

strFilter = "[Customer_Record_ID] = " &
[Forms]![frmSER]![cboCompany_Name].Column(1) 'as it is a numeric value

'Update form controls based on value selected in cboCompanyName combo box

Forms!frmSER![Customer_Phone] = Nz(DLookup("[Customer_Phone]",
"tblCustomers", strFilter))
Forms!frmSER![IndustryCode] = Nz(DLookup("[CustomerSIC]",
"tblCustomers", strFilter))
'Forms!frmSER![Company_Name] = Nz(DLookup("[Company_Name]",
"tblCustomers", strFilter))
Forms!frmSER![CustomerID] = Nz(DLookup("[CustomerID]", "tblCustomers",
strFilter))
Forms!frmSER![Address] = Nz(DLookup("[Address]", "tblCustomers",
strFilter))
Forms!frmSER![Address1] = Nz(DLookup("[Address1]", "tblCustomers",
strFilter))
Forms!frmSER![Address2] = Nz(DLookup("[Address2]", "tblCustomers",
strFilter))
Forms!frmSER![Address3] = Nz(DLookup("[Address3]", "tblCustomers",
strFilter))
Forms!frmSER![Address4] = Nz(DLookup("[Address4]", "tblCustomers",
strFilter))
Forms!frmSER![Address5] = Nz(DLookup("[Address5]", "tblCustomers",
strFilter))
Forms!frmSER![Address6] = Nz(DLookup("[Address6]", "tblCustomers",
strFilter))

[Forms]![frmSER]![FormSaved] = False 'the form can now be saved

End If
'return to form
End Function

And now get = error 3077 - syntax error (missing operator) in expression

at line

rst.FindFirst strsql

ummmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
I thank everyone for there help

Best Regards


Alex Dybenko said:
Hi,
at what line you are getting this error?

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Billp said:
Hi,
Can I do what I am hoping I can do with running a routine from a module on
the after update of a combo box.

What I am trying to do is catch if a new entry is there, and then not
allow
the form to be saved (latter issue), and if the entry is part of the
existing
database continue to populate data form that table to the forms controls.

The afterupdate is as follows
Call SER_Update ' from module

Me.Refresh


Then to the module
Sub SER_Update()

'user has entered a customer from the combo
'check if it is already in the customer database
'if it isn't then stop the enquiry form being saved
'by setting a flag
'until the new customer is added to the db and everything is updated

Dim rst As DAO.Recordset 'rsttblCustomers
Dim strsql As String
Dim strNewCust As String
Dim User_form As Access.Form 'users form

strNewCust = User_form!frmSER!cboCompany_Name

Set rst = CurrentDb().OpenRecordset("tblCustomers", dbOpenDynaset)
strsql = strNewCust
rst.FindFirst strsql

If rst.NoMatch Then 'the input is not in the list
' do something like set the save form to no until customer is added

User_form!frmSER!FormSaved = True 'sets the flag
'set up a messgage to notofy user that form cannot be saved

Else 'there is a match and customer is already in database

'populate fields with existinmg data

'by selecting the row you have effectually said start
Dim strFilter As String

' Evaluate filter before it is passed to Dlookup function.

strFilter = "[Customer_Record_ID] = " &
User_form!frmSER![cboCompany_Name].Column(1) 'as it is a numeric value

'Update form controls based on value selected in cboCompanyName combo
box

User_form!frmSER![Customer_Phone] = Nz(DLookup("[Customer_Phone]",
"tblCustomers", strFilter))
User_form!frmSER![IndustryCode] = Nz(DLookup("[CustomerSIC]",
"tblCustomers", strFilter))
'User_form!frmSER![Company_Name] = Nz(DLookup("[Company_Name]",
"tblCustomers", strFilter))
User_form!frmSER![CustomerID] = Nz(DLookup("[CustomerID]",
"tblCustomers", strFilter))
User_form!frmSER![Address] = Nz(DLookup("[Address]", "tblCustomers",
strFilter))
User_form!frmSER![Address1] = Nz(DLookup("[Address1]", "tblCustomers",
strFilter))
User_form!frmSER![Address2] = Nz(DLookup("[Address2]", "tblCustomers",
strFilter))
User_form!frmSER![Address3] = Nz(DLookup("[Address3]", "tblCustomers",
strFilter))
User_form!frmSER![Address4] = Nz(DLookup("[Address4]", "tblCustomers",
strFilter))
User_form!frmSER![Address5] = Nz(DLookup("[Address5]", "tblCustomers",
strFilter))
User_form!frmSER![Address6] = Nz(DLookup("[Address6]", "tblCustomers",
strFilter))

End If
'return to form
End Sub

What I am getting is an error 91 with a variable not set????????? on the
cboafterupdate.

Help please - any help appreciated
Thanks
Bill
Click to expand...
Click to expand...
 
Fantastic - all going good - yes
Many thanks and I am really appreciative of your help.
Best Regards
Bill

JimBurke via AccessMonster.com said:
The findFirst method needs to specify the value you're trying to find. I
don't know what the company name field is, but you need to do something like
this:

rst.FindFirst "companyFieldName = """ & cboCompany_Name & """"

That's 3 double quotes after

companyFieldName =

and 4 double quotes at then end. This way if you get a company name with a
single quote in it it will still work. I'm assuming the company name is a
text field. If it's numeric you don't need any quotes:

rst.FindFirst "companyFieldName = " & cboCompany_Name

This also assumes that cboCompany_Name has the actual name as the bound field
for the combobox. If it's not then you need to specify the column number, i.e.
if the 2nd column in the combobox has the name then it would be

rst.FindFirst "companyFieldName = """ & cboCompany_Name.column(1) & """"

column(1) would be used for the 2nd column because the column property is
zero-based

That should get you past the finFirst command anyway.

Here's a link that explains the FIndFirst command:

http://www.blueclaw-db.com/access_findfirst.htm
The error occured at the "Call" so it didn't seem to get to the routine.
Having said that
The error trapping didn't originally stop at a line untill I modified it.

The basis behind the question I have is from a brief.

"If the customer does not exist from the dropdown then the user will be
allowed to type in the customer name and other details as disussed by the
client. If the customer exists then the user will be allowed to enter a
contact name and the details will automatically fill. This is nexessary as we
do not want to stifle the conversation flow with the client."

With the suggestion from the reply from Jim Burke I can get into the routine,

"I think your User_form variable is your problem.You've declared that as a
form but never set it, and it's not needed anyway. I don't know that that's
even a valid way to refer to Forms. To reference controls on a form you
should just use

Forms!frmName!ctlName

The updated code is

Function SER_Update()

'user has entered a customer from the combo
'check if it is already in the customer database
'if it isn't then stop the enquiry form being saved
'by setting a flag
'until the new customer is added to the db and everything is updated

Dim db As DAO.Database
Dim rst As DAO.Recordset 'rsttblCustomers
Dim strsql As String
Dim strNewCust As String

strNewCust = [Forms]![frmSER]![cboCompany_Name]

MsgBox "here in the module"

Set db = CurrentDb
Set rst = db.OpenRecordset("tblCustomers", dbOpenDynaset)
strsql = strNewCust
rst.FindFirst strsql

If rst.NoMatch Then 'the input is not in the list
' do something like set the save form to no until customer is added

[Forms]![frmSER]![FormSaved] = True 'sets the flag
'set up a messgage to notify user that form cannot be saved
MsgBox "This Enquiry Cannot be saved untill you ADD the new customer."

Else 'there is a match and customer is already in database

'populate fields with existing data

'by selecting the row you have effectually said start
Dim strFilter As String

' Evaluate filter before it is passed to Dlookup function.

strFilter = "[Customer_Record_ID] = " &
[Forms]![frmSER]![cboCompany_Name].Column(1) 'as it is a numeric value

'Update form controls based on value selected in cboCompanyName combo box

Forms!frmSER![Customer_Phone] = Nz(DLookup("[Customer_Phone]",
"tblCustomers", strFilter))
Forms!frmSER![IndustryCode] = Nz(DLookup("[CustomerSIC]",
"tblCustomers", strFilter))
'Forms!frmSER![Company_Name] = Nz(DLookup("[Company_Name]",
"tblCustomers", strFilter))
Forms!frmSER![CustomerID] = Nz(DLookup("[CustomerID]", "tblCustomers",
strFilter))
Forms!frmSER![Address] = Nz(DLookup("[Address]", "tblCustomers",
strFilter))
Forms!frmSER![Address1] = Nz(DLookup("[Address1]", "tblCustomers",
strFilter))
Forms!frmSER![Address2] = Nz(DLookup("[Address2]", "tblCustomers",
strFilter))
Forms!frmSER![Address3] = Nz(DLookup("[Address3]", "tblCustomers",
strFilter))
Forms!frmSER![Address4] = Nz(DLookup("[Address4]", "tblCustomers",
strFilter))
Forms!frmSER![Address5] = Nz(DLookup("[Address5]", "tblCustomers",
strFilter))
Forms!frmSER![Address6] = Nz(DLookup("[Address6]", "tblCustomers",
strFilter))

[Forms]![frmSER]![FormSaved] = False 'the form can now be saved

End If
'return to form
End Function

And now get = error 3077 - syntax error (missing operator) in expression

at line

rst.FindFirst strsql

ummmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
I thank everyone for there help

Best Regards
Hi,
at what line you are getting this error?
Click to expand...
[quoted text clipped - 88 lines]
Thanks
Bill
Click to expand...
Click to expand...
Click to expand...
 
Back
Top