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" <(E-Mail Removed)> wrote in message
news:813B0A75-0590-41C2-A978-(E-Mail Removed)...
> 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
>
>
>

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" wrote:

> Hi,
> at what line you are getting this error?
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://accessblog.net
> http://www.PointLtd.com
>
>
> "Billp" <(E-Mail Removed)> wrote in message
> news:813B0A75-0590-41C2-A978-(E-Mail Removed)...
> > 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
> >
> >
> >
>

Fantastic - all going good - yes
Many thanks and I am really appreciative of your help.
Best Regards
Bill

"JimBurke via AccessMonster.com" wrote:

> 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
>
> Billp wrote:
> >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?
> >[quoted text clipped - 88 lines]
> >> > Thanks
> >> > Bill
>
> --
> Jim Burke
>
> Message posted via http://www.accessmonster.com
>
>