Changing field value

[Umoja]

Hey Guys,

Hopefully there is a simple answer to this question. Currently on my form I have a combo box [cboStatus]. The selection from the combo box is open, close, review etc. Once the selection is made from the combo box it assigns it to the account and insert the information in another table. The users do have access to the accounts on the form by a filter. I am using the Docmd.Run my Sql function to insert the information into the table. The question I have is if I wanted to change the status of the account from review to close, how can that be done? I thought by choosing a different status from the combo box [cboStatus] it would change it but it does not. Any information you guys can provide would be great. Thanks for all the help.

[MMcCarthy]

Hey Guys,

Hopefully there is a simple answer to this question. Currently on my form I have a combo box [cboStatus]. The selection from the combo box is open, close, review etc. Once the selection is made from the combo box it assigns it to the account and insert the information in another table.

How is this achieved. Is the control source of cboStatus set to the relevant field and what do you mean by another table?

Mary

[Umoja]

Hi mmccarthy,

Sorry for not explaining everything clearly. The control source of cboStatus is not set to the relevant field. This is how I have everything setup. The source for the form is a query [Custdetail]. The source for this query is the main table [Tblcustinfo] which contains all the fields. One of those fields is a text field called [CS], by default this field is set to N. Once a selection is made from the combo box [cboStatus], the cust#, CustName, AcctBal and the combo box selection is inserted into table [TblStaffAct]. In this table there is a field called Activity which is where is the combo box selection is inserted.
In query [Custdetail] there is a relationship between the main table [Tblcustinfo] and table [TblStaffAct]. The [CS] field and the [Activity] are included in the query and is displayed on the form. When a selection is made from the combo box the activity is displayed on the account and the [CS] field changes from N to Y. The code below is how I have programmed the combo box.

mySQL = "INSERT INTO TblStaffAct ([Cust#], CustName, Acctbal, cboStatus) 
Select [forms]![CustMain]![Cust#] as a, 
          [forms]![ CustMain]![CustName] as b, 
          [forms]![ CustMain]![Acctbal] as c, 
          [forms]![ CustMain]![cboStatus] as d;"
DoCmd.RunSQL mySQL

Hope this is more clearly. Thank you for helping me out again :)!

[MMcCarthy]

There are a couple of issues here. Firstly, you are inserting a new record in a table that is part of the query that is the record source for the form. Firstly, you will have to requery the form after the insert to update the record using:

Me.Requery

The other issue I have with this is that there can only be a one to one relationship between the two tables. If there are two records in the second table with a relationship to the first table this will create two records returned by the query.

Next there seems to be a problem with the insert query in that you have a space before the form name CustMain in three of the references. I would redesign it anyway as follows:

mySQL = "INSERT INTO TblStaffAct ([Cust#], CustName, Acctbal, cboStatus) VALUES (" & Me.[Cust#] & ",'" & Me.[CustName] "'," & Me.[Acctbal] & ",'" & Me.[cboStatus] & ");"
DoCmd.RunSQL mySQL

This assumes Cust# and Acctbal are numbers/currancy while the other two are text datatypes.

Mary

[Umoja]

Thank you, mmccarthy I made a few changes and I was able to get it to work.

[MMcCarthy]

Thank you, mmccarthy I made a few changes and I was able to get it to work.

That's great, glad to hear it.

Mary