Solved First create a record, second open a form (1 Viewer)

[gringope24]

Dears,
I have form with list box. Depends on the selection in listbox I want to create a new record by vba recordset. Record will be filled with information from a listbox. After thath I want to fire the form for this record in edit mode, so that user can input the rest fields.
Will it be fine way and will not bring me problems?
Reading the forum I found out that some of the solutions, which are the first idea for me, are not the best. This is the reason of my question

 

[theDBguy]

Hi. Don't need a recordset. Just open the form into a new record and either populate it from the listbox or set the default values to the listbox.

 

[gringope24]

I need to admit that selecting an item from listbox opens NEW form.
For me sending values from listbox to new form is not as elegant as creating record with code and then dispay form with this record.
Am I wrong or not notice something?

 

[theDBguy]

I need to admit that selecting an item from listbox opens NEW form.
For me sending values from listbox to new form is not as elegant as creating record with code and then dispay form with this record.
Am I wrong or not notice something?

When you open a form to new record and simply point that form to the listbox, it will appear similar to the effect you were trying to accomplish using code. So, all I am saying is you don't really need to use code. But, of course, you can use code, if you really want to.

 

[Pat Hartman]

Elegant? What you are doing is not elegant. Duplicating data that exists in another table violates normal forms

You have decided on the "solution" and asked us to code it or at least tell you how to code it. We don't even know the problem you are trying to solve.

To me, this sounds like your form should have a subform, and the subform should have a combo and that combo, should select the FK from there. Then the user fills in the blanks. THAT is elegant. You do it all without code. If you want the subform to show data related to the FK, you can.

I'm attaching a database with a many-to-many relationship. It shows how to implement the relationship using two different methods. From the perspective of tblA, I use a main form with a subform. From the perspective of tblB, I use a main form with a popup just so you could see the two methods.

 

[gringope24]

Thanks for all suggestions. However valuable it is, i feel that we misunderstood each other. Maybe I will discribe the functionality that I want to achieve:
1. Click an item on a listbox
2. Create a new record in sepearate table, which will be related to the item selected in listbox.
3. Open the form for just created record.

Previously I pass the PK of selected item to Open Args and open form in Add Mode. After that I retrieve PK from Open Args. Unfortunatelly, for this solution I recognize the problem with loading a subforms in new opened form, when use Add mode, therefore i started to seek another solution.

Appreciate any alternative possibility.

PS. Pat, I do not ask anyone for coding anything. ;-)

 

[theDBguy]

Can you post a sample db with test data?

 

[Pat Hartman]

I understood your description. It is poor practice to create "empty" records. Do you not require any validation code or will you just allow garbage to be entered in any field? Are no fields required? Garbage in Garbage out. You have visualized a method and are intent on implementing it.

Is your code creating the record correctly?
Are you capturing the autonumber of the new record?
What problem are you having with the OpenForm method?

 

[gringope24]

But I am wondering, if my question is unusual. I expect that it is quite common that user needs to choose an item from the list, create a record related to choosen item, displays the form and enter further data.
My database is used to store frequent quality measurements for equpment.

Service stuff is searching for a serial number of equipment. Search results are shown in listbox. User choose a correct serial number, then our form appear and he can enter results of measurements, dates, ambient conditions and etc. This is not rubish! This is specific values than can be entered only by the user manually.
Base on entered values he can generate datasheet for specific date and equipment. One serial number can have many, many measurements.

theDBguy, I will try to prepare version that I can share, but it will takes some time

 

[arnelgp]

you can enter the data directly to the "subform"

 

[gringope24]

OK, please see my db. Problem is with loading frmCriteria within frmProtocol.

When Open the db please click "New", search for serial number and choose some serial to load frmProtocol.

 

[The_Doc_Man]

If you make the selection from a main form, you can have a sub-form that uses the LinkParent and LinkChild fields so that you can automatically link the child (sub-) form to its parent (main) form. This would be best supported with a one-to-many relationship with the main form. You would fill in some field in the main form before creating the new record, in which case the link fields become your friends. You can then control the sub-form from the main form to create your new record and tell it to go to that new record. The main form can fill in what it needs to fill in and then can do a .SetFocus on the first blank control in the new record of the sub-form. OR you can just click into the sub-form. Either way, you can thento start entering data. This is very close conceptually to what you asked for, but maybe it does things in a slightly different order than you imagined. The main difference is main/sub vs two independent forms.

 

[theDBguy]

OK, please see my db. Problem is with loading frmCriteria within frmProtocol.

When Open the db please click "New", search for serial number and choose some serial to load frmProtocol.

Hi. Which version of Access are you using? When I click the New button, I get this error.

 

[gringope24]

Hi. Which version of Access are you using? When I click the New button, I get this error.

View attachment 98399

Microsoft® Access® for Microsoft 365 MSO (version 2111 comp. 16.0.14701.20278) 32-bit

 

[gringope24]

If you make the selection from a main form, you can have a sub- form that uses the LinkParent and LinkChild fields so that you can automatically link the child (sub-) form to its parent (main) form. This would be best supported with a one-to-many relationship with the main form. You would fill in some field in the main form before creating the new record, in which case the link fields become your friends. You can then control the sub-form from the main form to create your new record and tell it to go to that new record. The main form can fill in what it needs to fill in and then can do a .SetFocus on the first blank control in the new record of the sub-form. OR you can just click into the sub-form. Either way, you can thento start entering data. This is very close conceptually to what you asked for, but maybe it does things in a slightly different order than you imagined. The main difference is main/sub vs two independent forms.

Interesting but I cannot imagine it. Can you visualize it ?

 

[theDBguy]

Interesting but I cannot imagine it. Can you visualize it ?

Have you looked at the sample db @arnelgp posted in post #10? Do you have any questions about it?

 

[Pat Hartman]

I expect that it is quite common that user needs to choose an item from the list, create a record related to choosen item, displays the form and enter further data.

It is very common. We just don't do it the way you are requesting that it be done, You really should look at the m-m sample I posted earlier. It shows two ways of visualizing a m-m relationship.
Using a subform or a popup form is fine. The problem is that you want to create the record ahead of time and that means you need to save it "empty". That is the disconnect.

Also, it sounds like you are copying data from the combo and you should just be copying the ID to use as a FK. The "wrong" part of your request is insisting on saving the incomplete record. That's all. Just open a popup and pass in the FK from the listbox. In the BeforeInsert event of the popup form, populate the FK
Me.SomeID = Me.OpenArgs

Now you have a new empty record, linked to some table. You can add any other data you want and when you save the record, you can have proper validation code in the form's BeforeUpdate event that ensures that data is entered and valid. Creating the empty record first means you CANNOT properly validate the data.

 

[gringope24]

It is very common. We just don't do it the way you are requesting that it be done, You really should look at the m-m sample I posted earlier. It shows two ways of visualizing a m-m relationship.
Using a subform or a popup form is fine. The problem is that you want to create the record ahead of time and that means you need to save it "empty". That is the disconnect.

Also, it sounds like you are copying data from the combo and you should just be copying the ID to use as a FK. The "wrong" part of your request is insisting on saving the incomplete record. That's all. Just open a popup and pass in the FK from the listbox. In the BeforeInsert event of the popup form, populate the FK
Me.SomeID = Me.OpenArgs

Now you have a new empty record, linked to some table. You can add any other data you want and when you save the record, you can have proper validation code in the form's BeforeUpdate event that ensures that data is entered and valid. Creating the empty record first means you CANNOT properly validate the data.

At the biginning of the discussion i was thinking that passing PK from listbox and use it as FK is sufficient to create a new record. I was not thinking about validating the rest of the fields. I need to think about it.
Can you please explain way to use Before Insert?
Is it not enought to pass PK in OpenArgs, open form in Add Mode and onLoad retrieve OpenArgs to use in appropriate field?

 

[Pat Hartman]

It is important that you do not dirty the new record with code. That just confuses the user if he ends up trying to close the form and you give him error messages because data is missing. He "knows" he didn't type anything so why are you complaining. The BeforeInsert event doesn't run until after the user dirties the record. That way, he isn't confused by error messages regarding missing/invalid data. The other advantage is that it only runs for NEW records and it runs for Every new record so if the user adds multiple records, the FK gets populated correctly in all of them.

 

[gringope24]

Thank you for your answer it shed a light. I also dig into the forum and found out that BeforeInsert might be very useful.

My next concern is what if I have subform in which some of the buttons are showing/hiding depends on the specific characteristic of chosen serial number. Normally I have some code in OnLoad event which check the characteristic of product for specific serial number and adjust the buttons in subform. In AddNew mode, before user start typing, serial number is not know when form is loaded.
Shall I put this code in AfterInsert event? The same code stay in OnLoad event and it will be fired only when OpenArgs are empty?