Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1219
  • Last Modified:

Data entry into MS Access subform

Redoing a frontend/backend MS Access mdb setup with about 5 users.  I know there is a simple answer to this but it has eluded me for the past 5 hours.

I have a form, call it frmMaster, with a subform, call it frmSubform (easy enought right?)  The field SubformID forms a one-to-many relationship with the SubformID field in frmMaster.  Now this works great when i'm scrolling through the records, la la la, but everything bursts into flaming debris if i try to add a new record to frmSubform.  I get an 'Invalid use of Null' error message and the autonumber value that is entered into SubformID does not cascade into the recordset underlying frmMaster, even after refreshing.

Any thoughts on how to get around this?  I suppose I could open another form and use it for data entry but this is an app i'm developing for a call center so the less forms that a user has to open, the better.  I really just need to find some way around the inability to enter data into a subform.

Thanks in advance.
0
zhenchyld
Asked:
zhenchyld
  • 4
  • 4
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Do you have the Child/Master links on the subform control set to the corresponding ID's ?

mx
0
 
NathanIrwinCommented:
It sounds to me like you are using the SubForm data's Primary Key as your Link Field between the master form and the subform. You need to use the Foreign key in your subforms data source (which is usually the Primary Key for the master forms data source) as your link

Nathan
0
 
zhenchyldAuthor Commented:
NathanIrwin -  You are correct, i AM using the subform's data source's primary key to link to the master form.  I know this is a strange setup, but it's necessary because of how the CSR's workflow works.  The 'many' side of the relationship is actually the subform, as opposed to the main form.  Let me elaborate ...
The masterform is basically a list of phone messages or inbound calls the CSR's have taken.  The subform (there's actually about 4 of them but i'm just working with one for the moment) holds the customer and shipping information in the case that the call results in our having to send a replacement part for the customer's product.  Now, not every call results in a parts shipment, and each customer (in the subform) could possibly be associated with multiple calls, but i still need to have the call info on the master side of the relationship so the CSR's can scroll througth and see who needs to be called back, what calls need follow ups, etc ...

MX - pretty sure i do (if i don't i'm going to have a good, long, derisive laugh at myself) but i need to boot the laptop and get back to you on that one ... I'll check to make sure asap (of course this is unpaid overtime I'm putting in for a company that i'm leaving in seven days, so i'm not feeling too motivated, hee hee, might not happen until tomorrow)

regardless, thank you both for your quick replies.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NathanIrwinCommented:
zhenchyld
Is there any particular reason why you are not assigning a customer to the phone message in your main form instead of using a subform? Can more than one Customer be related to a phone message?
If only one Customer is related to each message, you would be better to assign the customer to the phone message in your main form, IMHO

Nathan
0
 
zhenchyldAuthor Commented:
NathanIrwin - yes and no.  we get some info from teh customer which is entered into the masterform record source, mainly just name and phone number.  the reason i'm trying to split the custoemr info into a different table is that only maybe ... twenty percent? maybe less? of the calls we get actually result in needing to take down any customer info.  Right now, that's what we're doing, and we have about ... 45,000 records in the main call table that are 95% empty fields.
0
 
NathanIrwinCommented:
zhenchyld
I agree that you should split the customer information from the "call details" table, if that is how is is currently designed, and create a seperate table for customer details. By then linking those two tables together all you then need is a Combo Box on your main form that allows you to assign an existing customer as required.

Nathan
0
 
zhenchyldAuthor Commented:
ha!  got it.

here's a workaround:

1.  Do not set the child/master relationship between the forms
2.  Write a query, somehting like "SELECT (subformdata) FROM (subformtable) WHERE (subformprimarykey) EQUALS (currentvalue in masterform foreign key field)", and set it as the datasource for the subform.
3.  Put a new in the record in the subform, then in the AfterInsert event of the subform, use some Vb to send hte new key value that was autogenerated in the subform into the Masterform's foreign key field, then refresh the masterform and requery the subform.

Works like a charm, for now, at least.
0
 
NathanIrwinCommented:
Ok, zhenchyld
I hope it all works out
Nathan
0
 
zhenchyldAuthor Commented:
lol thanks.  sometimes i just need a dialog to get teh juices flowing.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now