Solved

Set Subform Value

Posted on 2014-10-02
7
79 Views
Last Modified: 2016-06-26
I THINK this should be easy... but...

Have a form with a subform.  

The Form references a customer, the subform references a location (Customers could have more than one location).

Users were doing things to create blank records, so I set the state field on the location address to NOT NULL (SQL Backend).  Now, when adding a new customer, it's automatically trying to add a location.  

I need to either:
Stop the creation of a new Location automatically when creating a new customer (no clue how to do that).
OR
When someone clicks on the New Account button (Macro based, not Code based) how I can have the Macro based button automatically set the state value so as not to cause an error to pop up about NULLs.

Your assistance would be greatly appreciated!
0
Comment
Question by:Lee W, MVP
7 Comments
 
LVL 10

Expert Comment

by:t_hungate
ID: 40356980
Can you provide your table structure for clarification?

I am assuming there is a Customers table with a one to many relationship to another table containing locations but it sounds like you would want to add your customer first, then subsequently open a or access a method for inputting location data.

Maybe break down the addition of new customers into 2 steps, create new customer and populate Customers table, then open your form for location using your customer primary key.

You can also suppress the warning message if the form is loading properly and you just need to allow the user to access the location from to correct the NULL issue.

DoCmd.SetWarnings False

Open in new window


Hope this is somewhat helpful, more info is needed to be more exact.
0
 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40357064
Sorry, I thought I was pretty explicit.

Yes, Customer gets added first (why would you add a location customer's location before adding the customer?  Sorry, this logic escapes me).

Workflow cannot be significantly changed.  The database was existing and I came in to take over its development.  I'm good (not great) with access.
Illustration of Form
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40357242
To prevent subforms from trying to add records before the parent record is added, you need some code in the subform's BeforeInsert event.  Here's an example from on of my applications.  Check the autonumber on the parent form and if it is null, undo the typing in the subform and cancel the update.  I also move focus to the combo on the main form that is used to select a client but you may want to do something else.
Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent.txtClientID & "" = "" Then
        MsgBox "Please select a client first.", vbOKOnly
        Cancel = True
        Me.Parent.cboFindClient.SetFocus
        Exit Sub
    End If
End Sub

Open in new window


If subform records are being added without the user typing anything in them at all, then it is your own code that is dirtying the record and causing the problem.  You will need to examine the events of the subform to see where the problem is.  Post the code and we'll help you.

I know you asked for a macro but I don't do macros.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 95

Author Comment

by:Lee W, MVP
ID: 40357314
I don't do Macros either.  (I took over the project and am adjusting things where I can).

Thanks for the suggestion.  I'll look into how I might be able to incorporate your solution.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40357328
You really need to find the macro that is dirtying the subform and kill that.  Good Luck.

Personally, I would probably start by converting all the macros to VBA.  It will be crummy VBA but it will be easier to work with than macros.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40359318
In the sub form, delete the field linking the sub form record to the Main record.. In this case a sub form record is created only for the current parent record.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 41674831
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: PatHartman (https:#a40357328)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now