Update query based on a form value

[Terrence Carroll]

Hi,

I have a form with a textbox and a combo box. The text box is called
CompanyName and the combobox is called CompanyFamilyName. Every CompanyName
eventually needs to be assigned to a CompanyFamilyName. However some
CompanyNames have a "unassigned" value to the CompanyFamilyName to start
with. What I want to do is to give the user the ability to assign a new
CompanyFamilyName or to select a CompanyFamilyName from a combo box
containing a list of existing CompanyFamilyNames. I have this piece taken
care of. I also want the the CompanyFamilyName in the "Cumulative" table to
update based upon the value I enter on the form for the company family name
associated with the company name that previously had a value desingated as
"unassigned" for the company family name. Please let me know how i would do
this in the after update event of the combo box.

Thanks,

Terry Carroll

 

[Daryl S]

Terrence -

What is in the "Cumulative" table, and how does this normally get populated?
How is it related to the table/query that your form is based on?

If this is a query that pulls up summary information in a datasheet view,
then you do not need to do anything. If it is really a table, then let us
know what is in the table and how it is normally populated, then we can help
you with updating it for the changes to the CompanyFamilyName.

 

[Terrence Carroll]

The cumulative table is based off of a flat file excel spreadsheet that I
import into Access. This spreadsheet contains only the company name and does
not contain the company family name. I typically have the company family
name updated based upon an update query. This update query takes a look at
the COFAMNAME table which contain the known company family names that are
associated with individual company names. For any company names that do not
contain a company family name in the COFAMNAME table, I assign the value
"unassigned" to the company family name. At this point, I need the user to
take a look at all company names that have a value of "unassigned" in the
company family name field. They then can either indicate this company name
is related to a company family name in the COFAMNAME and establish that
relationship in the COFAMNAME table or create a new company family name if no
company family name exists in the COFAMNAME table related to the company
name.

 

[Daryl S]

Terrence -

Then I think what you want is to set up an update query and run it, like
this (but use your field and control names):

Dim strSQL as String

strSQL = "UPDATE COFAMNAME SET CompanyFamilyName = '" & _
Me.CompanyFamilyName & "' WHERE FamilyName = '" & _
Me.FamilyName & "' AND CompanyFamilyName = 'Unassigned';"

DoCmd.RunSQL strSQL

 

[Terrence Carroll]

I am very close to having this work. The only problem is that the AND
statement seems to be evaluated as an OR statement. Listed below is my code.
Unfortunately when I run this, it seems to update all CompanyFamilyNames
with "unassigned" to the CompanyFamilyName in the combo box when it should
just be looking to update the CompanyFamilyName for only the records where
the Company_Name on the form matches the Company_Name in the table and the
CompanyFamily Name in the table is "unassigned".

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "'Where Company_Name ='" & _
Me.Company_Name & "'AND CompanyFamilyName = 'Unassigned';"
DoCmd.RunSQL strSQL

 

[Daryl S]

Terrence -

Just missing a couple spaces, which would cause some problems. I would add
a debug.print in before the DoCmd statement, and if there are still issues,
post the results (copy/paste from the immediate window).

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "' WHERE Company_Name ='" & _
Me.Company_Name & "' AND CompanyFamilyName = 'Unassigned';"

Debug.Print strSQL

DoCmd.RunSQL strSQL

--
Daryl S

I am very close to having this work. The only problem is that the AND
statement seems to be evaluated as an OR statement. Listed below is my code.
Unfortunately when I run this, it seems to update all CompanyFamilyNames
with "unassigned" to the CompanyFamilyName in the combo box when it should
just be looking to update the CompanyFamilyName for only the records where
the Company_Name on the form matches the Company_Name in the table and the
CompanyFamily Name in the table is "unassigned".

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "'Where Company_Name ='" & _
Me.Company_Name & "'AND CompanyFamilyName = 'Unassigned';"
DoCmd.RunSQL strSQL

Terrence -

Then I think what you want is to set up an update query and run it, like
this (but use your field and control names):

Dim strSQL as String

strSQL = "UPDATE COFAMNAME SET CompanyFamilyName = '" & _
Me.CompanyFamilyName & "' WHERE FamilyName = '" & _
Me.FamilyName & "' AND CompanyFamilyName = 'Unassigned';"

DoCmd.RunSQL strSQL

 

[Terrence Carroll]

Listed below is what is showing up in the immediate window:

UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName ='TEST'
Where Company_Name ='dotstaff' AND CompanyFamilyName ='Unassigned';

Terrence -

Just missing a couple spaces, which would cause some problems. I would add
a debug.print in before the DoCmd statement, and if there are still issues,
post the results (copy/paste from the immediate window).

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "' WHERE Company_Name ='" & _
Me.Company_Name & "' AND CompanyFamilyName = 'Unassigned';"

Debug.Print strSQL

DoCmd.RunSQL strSQL

--
Daryl S

I am very close to having this work. The only problem is that the AND
statement seems to be evaluated as an OR statement. Listed below is my code.
Unfortunately when I run this, it seems to update all CompanyFamilyNames
with "unassigned" to the CompanyFamilyName in the combo box when it should
just be looking to update the CompanyFamilyName for only the records where
the Company_Name on the form matches the Company_Name in the table and the
CompanyFamily Name in the table is "unassigned".

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "'Where Company_Name ='" & _
Me.Company_Name & "'AND CompanyFamilyName = 'Unassigned';"
DoCmd.RunSQL strSQL

Terrence -

Then I think what you want is to set up an update query and run it, like
this (but use your field and control names):

Dim strSQL as String

strSQL = "UPDATE COFAMNAME SET CompanyFamilyName = '" & _
Me.CompanyFamilyName & "' WHERE FamilyName = '" & _
Me.FamilyName & "' AND CompanyFamilyName = 'Unassigned';"

DoCmd.RunSQL strSQL

--
Daryl S


:

The cumulative table is based off of a flat file excel spreadsheet that I
import into Access. This spreadsheet contains only the company name and does
not contain the company family name. I typically have the company family
name updated based upon an update query. This update query takes a look at
the COFAMNAME table which contain the known company family names that are
associated with individual company names. For any company names that do not
contain a company family name in the COFAMNAME table, I assign the value
"unassigned" to the company family name. At this point, I need the user to
take a look at all company names that have a value of "unassigned" in the
company family name field. They then can either indicate this company name
is related to a company family name in the COFAMNAME and establish that
relationship in the COFAMNAME table or create a new company family name if no
company family name exists in the COFAMNAME table related to the company
name.

:

Terrence -

What is in the "Cumulative" table, and how does this normally get populated?
How is it related to the table/query that your form is based on?

If this is a query that pulls up summary information in a datasheet view,
then you do not need to do anything. If it is really a table, then let us
know what is in the table and how it is normally populated, then we can help
you with updating it for the changes to the CompanyFamilyName.

--
Daryl S


:

Hi,

I have a form with a textbox and a combo box. The text box is called
CompanyName and the combobox is called CompanyFamilyName. Every CompanyName
eventually needs to be assigned to a CompanyFamilyName. However some
CompanyNames have a "unassigned" value to the CompanyFamilyName to start
with. What I want to do is to give the user the ability to assign a new
CompanyFamilyName or to select a CompanyFamilyName from a combo box
containing a list of existing CompanyFamilyNames. I have this piece taken
care of. I also want the the CompanyFamilyName in the "Cumulative" table to
update based upon the value I enter on the form for the company family name
associated with the company name that previously had a value desingated as
"unassigned" for the company family name. Please let me know how i would do
this in the after update event of the combo box.

Thanks,

Terry Carroll

 

[Daryl S]

Terrence -

The SQL looks good. Are you still getting other records updated? Can you
show a couple records before and after the code runs that should not be
updated? Let us know if you can tell what records are incorrectly updated.
In your last post you indicated it treated the AND like and OR. Does that
mean all 'dostaff' records get 'TEST' and all 'Unassigned' CompanyFamilyNames
are changed to 'TEST'?

--
Daryl S

Listed below is what is showing up in the immediate window:

UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName ='TEST'
Where Company_Name ='dotstaff' AND CompanyFamilyName ='Unassigned';

Terrence -

Just missing a couple spaces, which would cause some problems. I would add
a debug.print in before the DoCmd statement, and if there are still issues,
post the results (copy/paste from the immediate window).

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "' WHERE Company_Name ='" & _
Me.Company_Name & "' AND CompanyFamilyName = 'Unassigned';"

Debug.Print strSQL

DoCmd.RunSQL strSQL

--
Daryl S

I am very close to having this work. The only problem is that the AND
statement seems to be evaluated as an OR statement. Listed below is my code.
Unfortunately when I run this, it seems to update all CompanyFamilyNames
with "unassigned" to the CompanyFamilyName in the combo box when it should
just be looking to update the CompanyFamilyName for only the records where
the Company_Name on the form matches the Company_Name in the table and the
CompanyFamily Name in the table is "unassigned".

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "'Where Company_Name ='" & _
Me.Company_Name & "'AND CompanyFamilyName = 'Unassigned';"
DoCmd.RunSQL strSQL


:

Terrence -

Then I think what you want is to set up an update query and run it, like
this (but use your field and control names):

Dim strSQL as String

strSQL = "UPDATE COFAMNAME SET CompanyFamilyName = '" & _
Me.CompanyFamilyName & "' WHERE FamilyName = '" & _
Me.FamilyName & "' AND CompanyFamilyName = 'Unassigned';"

DoCmd.RunSQL strSQL

--
Daryl S


:

The cumulative table is based off of a flat file excel spreadsheet that I
import into Access. This spreadsheet contains only the company name and does
not contain the company family name. I typically have the company family
name updated based upon an update query. This update query takes a look at
the COFAMNAME table which contain the known company family names that are
associated with individual company names. For any company names that do not
contain a company family name in the COFAMNAME table, I assign the value
"unassigned" to the company family name. At this point, I need the user to
take a look at all company names that have a value of "unassigned" in the
company family name field. They then can either indicate this company name
is related to a company family name in the COFAMNAME and establish that
relationship in the COFAMNAME table or create a new company family name if no
company family name exists in the COFAMNAME table related to the company
name.

:

Terrence -

What is in the "Cumulative" table, and how does this normally get populated?
How is it related to the table/query that your form is based on?

If this is a query that pulls up summary information in a datasheet view,
then you do not need to do anything. If it is really a table, then let us
know what is in the table and how it is normally populated, then we can help
you with updating it for the changes to the CompanyFamilyName.

--
Daryl S


:

Hi,

I have a form with a textbox and a combo box. The text box is called
CompanyName and the combobox is called CompanyFamilyName. Every CompanyName
eventually needs to be assigned to a CompanyFamilyName. However some
CompanyNames have a "unassigned" value to the CompanyFamilyName to start
with. What I want to do is to give the user the ability to assign a new
CompanyFamilyName or to select a CompanyFamilyName from a combo box
containing a list of existing CompanyFamilyNames. I have this piece taken
care of. I also want the the CompanyFamilyName in the "Cumulative" table to
update based upon the value I enter on the form for the company family name
associated with the company name that previously had a value desingated as
"unassigned" for the company family name. Please let me know how i would do
this in the after update event of the combo box.

Thanks,

Terry Carroll

 

[Terrence Carroll]

Sample Record #1 - Prior to update query
Company Family Name
Unassigned
Company Name
dotstaff

What is - after update query - This is exactly how this record should be
updated
Company Family Name
Test
Company Name
dotstaff


Sample Record #2 - Prior to update query
Company Family Name
Unassigned
Company Name
JPMC

What is - After update query is run - This is not what should be
Company Family Name
Test
Company Name
JPMC

What should be - After update query is run
Company Family Name
Unassigned
Company Name
JPMC

Terrence -

The SQL looks good. Are you still getting other records updated? Can you
show a couple records before and after the code runs that should not be
updated? Let us know if you can tell what records are incorrectly updated.
In your last post you indicated it treated the AND like and OR. Does that
mean all 'dostaff' records get 'TEST' and all 'Unassigned' CompanyFamilyNames
are changed to 'TEST'?

--
Daryl S

Listed below is what is showing up in the immediate window:

UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName ='TEST'
Where Company_Name ='dotstaff' AND CompanyFamilyName ='Unassigned';

Terrence -

Just missing a couple spaces, which would cause some problems. I would add
a debug.print in before the DoCmd statement, and if there are still issues,
post the results (copy/paste from the immediate window).

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "' WHERE Company_Name ='" & _
Me.Company_Name & "' AND CompanyFamilyName = 'Unassigned';"

Debug.Print strSQL

DoCmd.RunSQL strSQL

--
Daryl S


:

I am very close to having this work. The only problem is that the AND
statement seems to be evaluated as an OR statement. Listed below is my code.
Unfortunately when I run this, it seems to update all CompanyFamilyNames
with "unassigned" to the CompanyFamilyName in the combo box when it should
just be looking to update the CompanyFamilyName for only the records where
the Company_Name on the form matches the Company_Name in the table and the
CompanyFamily Name in the table is "unassigned".

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "'Where Company_Name ='" & _
Me.Company_Name & "'AND CompanyFamilyName = 'Unassigned';"
DoCmd.RunSQL strSQL


:

Terrence -

Then I think what you want is to set up an update query and run it, like
this (but use your field and control names):

Dim strSQL as String

strSQL = "UPDATE COFAMNAME SET CompanyFamilyName = '" & _
Me.CompanyFamilyName & "' WHERE FamilyName = '" & _
Me.FamilyName & "' AND CompanyFamilyName = 'Unassigned';"

DoCmd.RunSQL strSQL

--
Daryl S


:

The cumulative table is based off of a flat file excel spreadsheet that I
import into Access. This spreadsheet contains only the company name and does
not contain the company family name. I typically have the company family
name updated based upon an update query. This update query takes a look at
the COFAMNAME table which contain the known company family names that are
associated with individual company names. For any company names that do not
contain a company family name in the COFAMNAME table, I assign the value
"unassigned" to the company family name. At this point, I need the user to
take a look at all company names that have a value of "unassigned" in the
company family name field. They then can either indicate this company name
is related to a company family name in the COFAMNAME and establish that
relationship in the COFAMNAME table or create a new company family name if no
company family name exists in the COFAMNAME table related to the company
name.

:

Terrence -

What is in the "Cumulative" table, and how does this normally get populated?
How is it related to the table/query that your form is based on?

If this is a query that pulls up summary information in a datasheet view,
then you do not need to do anything. If it is really a table, then let us
know what is in the table and how it is normally populated, then we can help
you with updating it for the changes to the CompanyFamilyName.

--
Daryl S


:

Hi,

I have a form with a textbox and a combo box. The text box is called
CompanyName and the combobox is called CompanyFamilyName. Every CompanyName
eventually needs to be assigned to a CompanyFamilyName. However some
CompanyNames have a "unassigned" value to the CompanyFamilyName to start
with. What I want to do is to give the user the ability to assign a new
CompanyFamilyName or to select a CompanyFamilyName from a combo box
containing a list of existing CompanyFamilyNames. I have this piece taken
care of. I also want the the CompanyFamilyName in the "Cumulative" table to
update based upon the value I enter on the form for the company family name
associated with the company name that previously had a value desingated as
"unassigned" for the company family name. Please let me know how i would do
this in the after update event of the combo box.

Thanks,

Terry Carroll

 

[Daryl S]

Terrence -

Yikes! I would put square brackets around the [CompanyFamilyName] and
[Company_Name] to be safe, since Name is a reserved word - grabbing at straws
here.

What happens if you copy/paste this into a blank SQL window (start a new
query, don't add any tables, go to View SQL, and paste this over the SELECT;
that is there:

UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName ='TEST'
Where Company_Name ='dotstaff' AND CompanyFamilyName ='Unassigned'

This was the result of your strSQL in the immediate window. Then switch to
design mode and see how it looks. Try running it. Does this work properly?
If not, check field names, etc. carefully. If this works, then check your
code again. Could there be anything running after the DoCmd.RunSQL statement
that could execute other queries? Try stepping through the code. Check the
table before and right after the DoCmd.RunSQL statement. If it is OK at this
point, keep stepping through the code to see where else something might
change the data.

Let us know what you find out. (I'll be unavailable now until Monday...)

--
Daryl S

Sample Record #1 - Prior to update query
Company Family Name
Unassigned
Company Name
dotstaff

What is - after update query - This is exactly how this record should be
updated
Company Family Name
Test
Company Name
dotstaff


Sample Record #2 - Prior to update query
Company Family Name
Unassigned
Company Name
JPMC

What is - After update query is run - This is not what should be
Company Family Name
Test
Company Name
JPMC

What should be - After update query is run
Company Family Name
Unassigned
Company Name
JPMC

Terrence -

The SQL looks good. Are you still getting other records updated? Can you
show a couple records before and after the code runs that should not be
updated? Let us know if you can tell what records are incorrectly updated.
In your last post you indicated it treated the AND like and OR. Does that
mean all 'dostaff' records get 'TEST' and all 'Unassigned' CompanyFamilyNames
are changed to 'TEST'?

--
Daryl S

Listed below is what is showing up in the immediate window:

UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName ='TEST'
Where Company_Name ='dotstaff' AND CompanyFamilyName ='Unassigned';

:

Terrence -

Just missing a couple spaces, which would cause some problems. I would add
a debug.print in before the DoCmd statement, and if there are still issues,
post the results (copy/paste from the immediate window).

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "' WHERE Company_Name ='" & _
Me.Company_Name & "' AND CompanyFamilyName = 'Unassigned';"

Debug.Print strSQL

DoCmd.RunSQL strSQL

--
Daryl S


:

I am very close to having this work. The only problem is that the AND
statement seems to be evaluated as an OR statement. Listed below is my code.
Unfortunately when I run this, it seems to update all CompanyFamilyNames
with "unassigned" to the CompanyFamilyName in the combo box when it should
just be looking to update the CompanyFamilyName for only the records where
the Company_Name on the form matches the Company_Name in the table and the
CompanyFamily Name in the table is "unassigned".

strSQL = "UPDATE [VMS Job Order Listing Cumulative] SET CompanyFamilyName =
'" & _
Me.cboCOFAMNAME & "'Where Company_Name ='" & _
Me.Company_Name & "'AND CompanyFamilyName = 'Unassigned';"
DoCmd.RunSQL strSQL


:

Terrence -

Then I think what you want is to set up an update query and run it, like
this (but use your field and control names):

Dim strSQL as String

strSQL = "UPDATE COFAMNAME SET CompanyFamilyName = '" & _
Me.CompanyFamilyName & "' WHERE FamilyName = '" & _
Me.FamilyName & "' AND CompanyFamilyName = 'Unassigned';"

DoCmd.RunSQL strSQL

--
Daryl S


:

The cumulative table is based off of a flat file excel spreadsheet that I
import into Access. This spreadsheet contains only the company name and does
not contain the company family name. I typically have the company family
name updated based upon an update query. This update query takes a look at
the COFAMNAME table which contain the known company family names that are
associated with individual company names. For any company names that do not
contain a company family name in the COFAMNAME table, I assign the value
"unassigned" to the company family name. At this point, I need the user to
take a look at all company names that have a value of "unassigned" in the
company family name field. They then can either indicate this company name
is related to a company family name in the COFAMNAME and establish that
relationship in the COFAMNAME table or create a new company family name if no
company family name exists in the COFAMNAME table related to the company
name.

:

Terrence -

What is in the "Cumulative" table, and how does this normally get populated?
How is it related to the table/query that your form is based on?

If this is a query that pulls up summary information in a datasheet view,
then you do not need to do anything. If it is really a table, then let us
know what is in the table and how it is normally populated, then we can help
you with updating it for the changes to the CompanyFamilyName.

--
Daryl S


:

Hi,

I have a form with a textbox and a combo box. The text box is called
CompanyName and the combobox is called CompanyFamilyName. Every CompanyName
eventually needs to be assigned to a CompanyFamilyName. However some
CompanyNames have a "unassigned" value to the CompanyFamilyName to start
with. What I want to do is to give the user the ability to assign a new
CompanyFamilyName or to select a CompanyFamilyName from a combo box
containing a list of existing CompanyFamilyNames. I have this piece taken
care of. I also want the the CompanyFamilyName in the "Cumulative" table to
update based upon the value I enter on the form for the company family name
associated with the company name that previously had a value desingated as
"unassigned" for the company family name. Please let me know how i would do
this in the after update event of the combo box.

Thanks,

Terry Carroll