Subform on data-entry form (1 Viewer)

Babycat

Member
Local time
Today, 21:33
Joined
Mar 31, 2020
Messages
275
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!
 

Attachments

  • Example.png
    Example.png
    42.4 KB · Views: 144
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:33
Joined
Oct 29, 2018
Messages
21,357
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

Member
Local time
Today, 21:33
Joined
Mar 31, 2020
Messages
275
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
Code:
        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
 

Attachments

  • Error_438.PNG
    Error_438.PNG
    4 KB · Views: 209

theDBguy

I’m here to help
Staff member
Local time
Today, 07:33
Joined
Oct 29, 2018
Messages
21,357
Hi theDBguy
It is something like this
Code:
        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

Member
Local time
Today, 21:33
Joined
Mar 31, 2020
Messages
275
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
Code:
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’m here to help
Staff member
Local time
Today, 07:33
Joined
Oct 29, 2018
Messages
21,357
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
Code:
rs!Attachments = !Attachments
Okay, yes, that makes sense. Take a look at this article to see what I meant about using LoadFromFile.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:33
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 21:33
Joined
Mar 31, 2020
Messages
275
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...
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:33
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 21:33
Joined
Mar 31, 2020
Messages
275
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
Code:
rs!customerID = me.Txt_Addnew_cusID
rs!property1 = me.Txt_Addnew_TypeA_property1
rs!property2 = me.Txt_Addnew_TypeA_property2
rs!modifieddate = Date()
....
 

Attachments

  • Schema.PNG
    Schema.PNG
    18.4 KB · Views: 197
  • SubFrom_Entry.zip
    57.9 KB · Views: 118

Babycat

Member
Local time
Today, 21:33
Joined
Mar 31, 2020
Messages
275
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.
 

Attachments

  • Crying.PNG
    Crying.PNG
    18.4 KB · Views: 216

Users who are viewing this thread

Top Bottom