Solved

Set Subform Value

Posted on 2014-10-02
7
88 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 35

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 35

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 27

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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