Data Entry Form

  • Thread starter Thread starter karen scheu via AccessMonster.com
  • Start date Start date
K

karen scheu via AccessMonster.com

I need to allow users to add/edit data from a table. The table is used to
store comments related to orders. The master table contains all the order
information and the user does not have update access. I want the user to
select the order number (from a list of orders within the master table),
then if the order is found in the comments table, allow the user to edit
the information. If no order is found in the comments table, then allow
user to add. How would I accomplish this? Do I need to use VBA to find
the record? Should I have a form for the order selection and then opens
either a form for update or a form for adding?

Any help is appreciated.

Karen
 
You could solve this with a macro with 3 lines using the condition column:

MyForm refers to the form where the user selects the order number

Line 1
--Condition
DCount("[Order_No]","Order_Comment_tab","[Order_No]=" &
forms!MyForm!Order_No & "")>0
--Action
openform
Form Name Order_Comments_frm
Where Condition [Order_No] = forms!MyForm!Order_No

Line 2
--Condition
DCount("[Order_No]","Order_Comment_tab","[Order_No]=" &
forms!MyForm!Order_No & "")>0
--Action
SetValue
Item [Forms]![Order_Comments_frm].[AllowAdditions]
Expression False

Line 3
--Condition
DCount("[Client_Id]","Client_Tab","[Client_Id]='" & "111" & "'")=0
--Action
OpenForm
Form Name Order_Comments_frm
Data Mode Add

On the form create an event after the insert that says:

if me.allowaddions then
me.allowadditions = false
end if

If your order is type text, just add ' in the conditions for the macro right
after the = sign, and when closing the criteria for the dcount function like
this:
DCount("[Order_No]","Order_Comment_tab","[Order_No]='" &
forms!MyForm!Order_No & "'")>0

Call this macro from the form where the user selects the order number
 
Thanks very much. I got this to work. What about error handling? This is
a multiuser update. Do I need to program for any errors?

Thanks
 
You can convert the macro to visual basic code and then add the error handler
to the function created, to convert a macro to vb use tools\macro\convert
macros to visual basic, however this macro should not have problems in a
multiuser environment
 
Back
Top