By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Changing field value

P: 24
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.
Jan 15 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 15 '07 #2

P: 24
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.

Expand|Select|Wrap|Line Numbers
  1. mySQL = "INSERT INTO TblStaffAct ([Cust#], CustName, Acctbal, cboStatus) 
  2. Select [forms]![CustMain]![Cust#] as a, 
  3.           [forms]![ CustMain]![CustName] as b, 
  4.           [forms]![ CustMain]![Acctbal] as c, 
  5.           [forms]![ CustMain]![cboStatus] as d;"
  6. DoCmd.RunSQL mySQL
Hope this is more clearly. Thank you for helping me out again :)!
Jan 15 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. mySQL = "INSERT INTO TblStaffAct ([Cust#], CustName, Acctbal, cboStatus) VALUES (" & Me.[Cust#] & ",'" & Me.[CustName] "'," & Me.[Acctbal] & ",'" & Me.[cboStatus] & ");"
  2. DoCmd.RunSQL mySQL
This assumes Cust# and Acctbal are numbers/currancy while the other two are text datatypes.

Mary
Jan 17 '07 #4

P: 24
Thank you, mmccarthy I made a few changes and I was able to get it to work.
Jan 22 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Jan 22 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.