472,988 Members | 2,577 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,988 software developers and data experts.


I am using Access 97 on a PC running Windows NT 4.0 SP6a.

I have some code (shown below) intended to add a set of records to one
table (tblGradeProps) when a new record is created in another

The oddity is that when the procedure Detail_Click is called from the
procedure cmbMktSect_Exit, the RunSQL statement doesn't add the
required records but it does so when invoked by the Detail_Click

I've been messing with this for days without success. At one time I
was using an Append Query with DoCmd.OpenQuery and while it wouldn't
add records when called by the code it did so when opened manually
from the database window. Now the query is just a convenient place to
store the SQL string.

Any help or suggestions will be gratefully received.

************START OF CODE FRAGMENT**********************
Private Sub cmbMktSect_Exit(Cancel As Integer)
'this field can be exited on all records so we need to find out if
we're dealing with a new record
If Me![cmbMktSect].BackColor = vbYellow Then 'this is a new
'we're dealing with a new record and must add property record to
the table "tblGradeProps"

'reset the grade and sector fields to the default condition.
Me![cmbMktSect].Locked = True
Me![Grade].Locked = True
Me![Grade].BackColor = vbWhite
Me![cmbMktSect].BackColor = vbWhite

Debug.Print "Called from cmbMktSect_Exit"

Call detail_click
'reset the record source so that the market sector text can be
cmbMktSect.RowSource = "tblMktSector"

'reset the drop-down menu to show only 1 row
Me![cmbMktSect].ListRows = 1

End If
End Sub

Sub detail_click()
Dim strSQL As String

strSQL = CurrentDb.QueryDefs("qryAppSecProps").SQL

Debug.Print Me![cmbMktSect]
Debug.Print Me![GradeID]
Debug.Print strSQL

DoCmd.RunSQL strSQL
'DoCmd.OpenQuery "qryAppSecProps", acViewNormal, acAdd
'Debug.Print CurrentDb.QueryDefs("qryAppSecProps").SQL

End Sub
***************END OF CODE FRAGMENT
Nov 12 '05 #1
0 8183

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Rotsj | last post by:
Hi, i try to execute an update query from within a form, but i get the message: run time error '3144'. Syntax error on the update statement when i try something like this: DoCmd.RunSQL...
by: Andy | last post by:
Hello, I am running an sql statement that INSERTS INTO a table. If I run the query using docmd.runSQL, it works fine - new records are added to the table and duplicate records are disregared (...
by: Pathfinder | last post by:
Hi All I am trying to run the below but I receive the following error "runsql action requires an argument consisting of an SQL statment" Dim MySQL$ MySQL$ = "Select * from mytablename"...
by: Richard Hollenbeck | last post by:
Help! I don't know why this isn't working: Private Sub Combo9_Change() Dim UsersCourseSelection As String UsersCourseSelection = Me("Combo9").Value Combo13.Visible = True 'the following...
by: RC | last post by:
In my Access 2002 form, I have a combo box and on the AfterUpdate event I use DoCmd.RunSQL ("UPDATE .... to update records in a table. When it starts to run I get a message "You are about to...
by: David | last post by:
I am trying to insert an employee number into the EmpNbr field in my main table from a form where I add a new employee to my employee table. I was hoping this command would work, but it isn't. ...
by: ben | last post by:
I have the following code in a VBA module: DoCmd.RunSql "Update tData Set sd = Log(Strike/Price) where symbol = '" & symbol & "'" This statement worked fine, and was using the built in math...
by: jl2886 | last post by:
Hello. I have two questions: Private Sub Form_Load() Dim assID As Long assID = DMax("CLng(Right(,3))", "Master_Log") Me.LSI_Case_Number = Month(Date) & Format(Year(Date), "yy") & "-" &...
by: natural | last post by:
Good Afternoon I have an option grou[ and on the after update i would like to provide the user with a msgbox, and then action placed my docmd.setwarnings false everywhere, but i still get my...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.