Access Form Coding

[Guest]

I want to write a event on the find button on MSAccess form

Below code gives me error
'2342'
A runsql action requires an argument consisting of an SQL statement.

Private Sub Find_LostFocus()
DoCmd.RunSQL ("select * from tbl_WC_wCandS_KPWCmatch where
patient_mrn='" & REC_ISN & "'")
End Sub

Please help
Madhuri

 

[Dirk Goldgar]

I want to write a event on the find button on MSAccess form

Below code gives me error
'2342'
A runsql action requires an argument consisting of an SQL statement.

Private Sub Find_LostFocus()
DoCmd.RunSQL ("select * from tbl_WC_wCandS_KPWCmatch where
patient_mrn='" & REC_ISN & "'")
End Sub

Please help
Madhuri

The RunSQL method can only be used to execute an action query, not a
SELECT query. What are you actually wanting this event to do? It seems
odd that you'd be using the LostFocus event of a command button.

 

[Guest]

Hi Dirk

I wanted to find a record by placing a find record button on the form and
write code in lostfocus event .. isnt that right ? I would also like to know
If I want to write code to a Save button click event for if the child record
dosent exist give a message and dont save record... how do I do that thru
access forms ?

Thanks in advance
Madhuri

 

[Dirk Goldgar]

Hi Dirk

I wanted to find a record by placing a find record button on the form
and write code in lostfocus event .. isnt that right ? I would also
like to know If I want to write code to a Save button click event for
if the child record dosent exist give a message and dont save
record... how do I do that thru access forms ?

Thanks in advance
Madhuri

Normally you'd use a button's Click event, not its LostFocus event,
perform some action. LostFocus will fire if the user just tabs through
the button without clicking on it, and will not fire when the user
clicks on the button, until the user later moves the focus off the
button. Unless you're trying to do something very unusual, you should
use the Click event.

If by "find a record" you mean you want your form to move to the record
specified by the REC_ISN control, then you would use code something like
this:

'----- start of example code -----
Private Sub Find_Click()

With Me.RecordsetClone

.FindFirst "patient_mrn='" & REC_ISN & "'"

If .NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = .Bookmark
End If

End With

End Sub
'----- end of example code -----

That said, I'm not sure whether this is what you're really trying to do
or not. You'd need to give more details.

And I haven't the faintest idea what you are asking in your second
question. What "child record"? What would you be saving or not saving?

 

[Guest]

Hi Dirk

Thank you for guiding me thru the find button code

Sorry for asking my second question really vague..

I have a main form and child subforms eg the Department and employees in the
department form. If I add a department and press save I want to check whether
at least one employee has been entered in that department when the user
clicks on save button to save record. If there is no employee record I would
say
Msgbox " Cant save record " and rollback

How do I do that in Access coding.

Thanks
Madhuri

 

[Guest]

Hi Dirk

What does a recordsetclone mean ? how could I find similar functions and
help on the functions. Please refer any websites where I could get a complete
look into these functions.

Thanks
Madhuri

 

[Dirk Goldgar]

Hi Dirk

Thank you for guiding me thru the find button code

Sorry for asking my second question really vague..

I have a main form and child subforms eg the Department and employees
in the department form. If I add a department and press save I want
to check whether at least one employee has been entered in that
department when the user clicks on save button to save record. If
there is no employee record I would say
Msgbox " Cant save record " and rollback

How do I do that in Access coding.

This is not a simple problem. If you have, say, a Departments table and
a DepartmentEmployees table, related one-to-many, then you can't enter
employees in for the department (in DepartmentEmployees) until the
department has been saved in Departments. There are only two workable
approaches that I know of.

(1) Go ahead and add the department, then add employees for it, and then
at some point -- maybe when you close the form -- check whether there
are any departments without employees. If there are, at that point you
can offer the user the choice of adding employees for that department
now, or else deleting the department.

(2) Base your form and subform on work tables, not the "real"
Departments and DepartmentEmployees table. Now your "Save" button
copies the records from these work tables to the "real" tables, while
rolling back is simply a matter of emptying the work tables.

 

[Dirk Goldgar]

Hi Dirk

What does a recordsetclone mean ? how could I find similar functions
and help on the functions. Please refer any websites where I could
get a complete look into these functions.

RecordsetClone is a property of the Access Form object, which returns a
"clone" -- essentially a duplicate copy -- of the form's recordset. A
Recordset is a programming object that holds a set of records, such as
might be returned from a table or query. Each bound form has a
recordset behind the scenes, holding the records that have been
extracted from the form's RecordSource for display on the form.

In recent versions of Access, both Recordset and RecordsetClone are
available as properties of a form. The Recordset property gives you a
reference directly to the recordset object being used by the form
itself, while RecordsetClone gives you, as I said, a reference to a
duplicate copy of that recordset. Operations on the form's recordset
itself can be tricky, though you can use it to move the form to a
particular record. It's often better to work with the form's
recordsetclone, though, because you can navigate in that object without
affecting the form itself until you synchronize the bookmarks.

Information like this is available in the Access help files, under the
topic "Programming in Visual Basic" -> "Microsoft Access Visual Basic
Reference". Also, manipulation of recordsets is likely to involve DAO
(Data Access Objects), so you'd also want to read the topic "Microsoft
Data Access Objects 3.60". You may also come across another type of
recordset manipulaton, using ActiveX Data Objects, or ADO. This is
roughly equivalent to DAO in function -- more general, but less
efficient and powerful in dealing specifically with Access databases --
and has its own set of programming objects including a Recordset object.
However, ADO objects and DAO objects are not interchangeable, which ofte
n leads to programming errors.