Solved

Set Subform Value

Posted on 2014-10-02
7
85 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 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 Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

947 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

24 Experts available now in Live!

Get 1:1 Help Now