Subform on data-entry form

[Babycat]

Hello Everyone,

My idea is making data-entry form which consists of a main form and subform for entering customer information as attached picture.
There are many type of customer, it is selected from combo-box.
For example, I am entering info of a customer which type A. I select type A from combo box, then set subform sourceobject to the form name: TypeA.
However, my first issue is that as soon as I click on any sub-form's control, the main form seems lost focus and it saves data (Customer name, ID, Type, photo...) immediately. Therefore, the Cancel button becomes useless, I could not cancel the operation since the data is already saved on table "Customer"

I have tried to unbound both main and subform, saving data manually in On_click_event_OK_button by using recordset method. But other issue is that recordset method is not working with attachment type (customer photo)
So, any idea to help me overcome this?
My actual project is with about 20 fields and 10 customer types.

Thank everyone!

 

[theDBguy]

But other issue is that recordset method is not working with attachment type (customer photo)

Hi. There are other concerns with what you said, but I'll concentrate on this one. You can totally save attachments into tables using recordsets. What does your code look like?

 

[Babycat]

Hi. There are other concerns with what you said, but I'll concentrate on this one. You can totally save attachments into tables using recordsets. What does your code look like?

Hi theDBguy
It is something like this

        SQLtbl = "Customer"
        Set rs = db.OpenRecordset(SQLtbl)
            rs!Fullname = !Txt_AddCus_FullName
            rs!Type = !Cbx_AddCus_Type
            rs!Attachments = !Attachments
            rs!CurrJob = !Txt_AddCus_Job
            rs!Phone = !Txt_AddCus_Phone

The error is at linecode #5, Runtime error 438

 

[theDBguy]

Hi theDBguy
It is something like this

        SQLtbl = "Customer"
        Set rs = db.OpenRecordset(SQLtbl)

            rs!Fullname = !Txt_AddCus_FullName
            rs!Type = !Cbx_AddCus_Type
            rs!Attachments = !Attachments
            rs!CurrJob = !Txt_AddCus_Job
            rs!Phone = !Txt_AddCus_Phone

The error is at line #3, Runtime erroe 438

Hi. If by "line 3," you mean this one: rs!Fname = !Txt_AddCus_FullName. That's because you're missing a With/End With block. If you don't want to use a With/End With block, then fully qualify your objects. For example:
Me!Txt_AddCus_FullName
However, using Me!Attachments won't work. You'll have to use the LoadFromFile method.

 

[Babycat]

Hi. If by "line 3," you mean this one: rs!Fname = !Txt_AddCus_FullName. That's because you're missing a With/End With block. If you don't want to use a With/End With block, then fully qualify your objects. For example:
Me!Txt_AddCus_FullName
However, using Me!Attachments won't work. You'll have to use the LoadFromFile method.

I am so sorry , I have editted the post with correct line number, my actual code has with/end with block. Other fields are saved successfully when I commented out the line

rs!Attachments = !Attachments

back to my original post, is that possible to use bounded form? Becoz I intend to use this design to edit existing customer also. I think the bounded form will save a lot work...

 

[theDBguy]

I am so sorry , I have editted the post with correct line number, my actual code has with/end with block. Other fields are saved successfully when I commented out the line

rs!Attachments = !Attachments

Okay, yes, that makes sense. Take a look at this article to see what I meant about using LoadFromFile.

Work with attachments in DAO

Office VBA reference topic

docs.microsoft.com

 

[Pat Hartman]

You appear to have a design flaw in your schema and also a misunderstanding of how relationships work. If you are enforcing relationships and you should be, you MUST save the parent record before you can create a child record. Access is helping you to do that by saving the main form record when you click into the subform control.

The design flaw is that you have data in the two "Type" table names. Table names should NEVER contain data. You also said you have 10 types which implies that you will have ten "type" tables. I would step back and do some reading on normalization before embarking on this path. If you will share more regarding "type", we can help you make a better design before you move on.

 

[Babycat]

You appear to have a design flaw in your schema and also a misunderstanding of how relationships work. If you are enforcing relationships and you should be, you MUST save the parent record before you can create a child record. Access is helping you to do that by saving the main form record when you click into the subform control.

The design flaw is that you have data in the two "Type" table names. Table names should NEVER contain data. You also said you have 10 types which implies that you will have ten "type" tables. I would step back and do some reading on normalization before embarking on this path. If you will share more regarding "type", we can help you make a better design before you move on.

Dear Pat

Yes, I am a newbie in ms access, I am much appreciated your all comments to make my project better.
My objective is to create a data-entry form which work with any customer type.
The main-form (customer table) contains all general information such as customer name, address, phone, email, DOB, POB... while the "type table" is more specified about each type of customer. Sorry my poor english, the term "customer type" may not really accurate here.

For example: Table typeA is about furniture customer, its properties would be: prefered wood, age of wood, shipping distance, color... Table typeB is about fashion customer with properties: prefered origin country, material, order amount range...Table typeC is about electronics customer with its own properties and so on...
It means each customer type has its specified properties. In that case, What would be the best schema design?
FYI: In my first design, I have only 2 customer types, so I did not use the subform, I let the controls overlaping in a certain screen area, and hide/show them according customer type selected in combo box. But with 10 types of customer, I dont think it is good design...

 

[arnelgp]

what is ID on each Tables?
if autonumber, then the relationship is incorrect.
remove the Relationship.

only 1 table for customer type:

1. tblCustomerType
ID (autonumber)
Customer Type (A or B)

2. tblCustomer
ID (autonumber)
CustomerTypeID (Long, FK to tblCustomerType)
..other fields

3. tblMaterials (common for all customer types)
ID (autonumber)
CustomerTypeID (long, FK to tblCustomerType)
Material Ident (string)
Material Description (string)

4.tblCustMaterials (junction table between customers and materials)
CustID (long, FK to tblCustomer)
MatID (long, FK to tblMaterials)
Quantity Reqd (double)

5.tblMaterialsUsed (actual issuance of materials, can chk tblCustMaterials for the Reqd Qty) @ the end you can generate report
of discrepancy if you go-over the required or less than required to finished to project.
CustID (long)
MatID (long)
Quantity Used (double)

 

[Babycat]

Hi Arnelgp
I am not sure if I misunderstood you. Is it like attached schema? If yes, then I have other question...

what is ID on each Tables?
if autonumber, then the relationship is incorrect.
remove the Relationship.

In first version, I didnt use subform, I have a suggestion button to generate ID for the main from. This main form's recordsouce is table "customer", so once I close the form, data will be automatically saved to tbl customer. Table typeA is saved by recordsets method that I mentioned in earlier post like

rs!customerID = me.Txt_Addnew_cusID
rs!property1 = me.Txt_Addnew_TypeA_property1
rs!property2 = me.Txt_Addnew_TypeA_property2
rs!modifieddate = Date()
....

 

[Babycat]

I cry until no more tear...
Finally, I decided no subform. Let the controls crazy overlap each other... It becoz my first version have released. I dont want to take risk to change the relational schema or even form design.
Thank everyone for helping me.