Open form to New Record but with a specific value in text box. (1 Viewer)

ddewald

New member
Local time
Today, 12:26
Joined
Jan 31, 2020
Messages
19
I have a form called "frm_Vendors" that lets you select a vendor from a combobox and then displays the vendors details. I would like to open another form called "frm_NewPurchase" that opens to a new record, but autofills the "VendorNo" text box with the "VendorID" from the previous form. Can this be done?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:26
Joined
May 7, 2009
Messages
19,169
add OpenArgs parameter when you open "frm_NewPurchase" passing the VendorID:

docmd.OpenForm FormName:="frm_NewPurchase", OpenArgs:=Me.cboVendorID

add code to the Load Event of "frm_NewPurchase"
Code:
Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
    Me.VendorNo = Me.OpenArgs
End If
End Sub
 

ddewald

New member
Local time
Today, 12:26
Joined
Jan 31, 2020
Messages
19
That worked great to bring up the proper vendor but it autofilled the rest of the form with the last record. Is there a way to have it open to a new record while diplaying the VendorNo?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:26
Joined
Oct 29, 2018
Messages
21,358
Hi. You could use the acFormAdd in the DataMode argument. Also, I would recommend using the DefaultValue property to avoid creating blank records.
 

ddewald

New member
Local time
Today, 12:26
Joined
Jan 31, 2020
Messages
19
Hi. You could use the acFormAdd in the DataMode argument. Also, I would recommend using the DefaultValue property to avoid creating blank records.

Hate to be a pain, but I'm very green when it comes to coding. Would you be willing to spell that out a bit more for me? Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:26
Joined
Oct 29, 2018
Messages
21,358
Hate to be a pain, but I'm very green when it comes to coding. Would you be willing to spell that out a bit more for me? Thank you!
Something like:

DoCmd.OpenForm FormName:="frm_NewPurchase", OpenArgs:=Me.cboVendorID, DataMode:=acFormAdd

And in the Load event you already have, just add the bolded part below:

Me.VendorNo.DefaultValue = """" & Me.OpenArgs & """"
 

ddewald

New member
Local time
Today, 12:26
Joined
Jan 31, 2020
Messages
19
Hi. You're welcome. Arnel and I were happy to assist. Good luck with your project.

Having an issue. It worked at first, but as soon as I start to fill out the form, the VendorNo goes to 0.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:26
Joined
Oct 29, 2018
Messages
21,358
Having an issue. It worked at first, but as soon as I start to fill out the form, the VendorNo goes to 0.
Hi. You might consider posting a demo version of your db, so we can take a look.
 

zeroaccess

Active member
Local time
Today, 11:26
Joined
Jan 30, 2020
Messages
671
That worked great to bring up the proper vendor but it autofilled the rest of the form with the last record. Is there a way to have it open to a new record while diplaying the VendorNo?

On click of the button:

Code:
Private Sub cmdNewPurchase_Click()
    DoCmd.OpenForm "frm_NewPurchase", acNormal, , 1 = 0, , acDialog
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:26
Joined
May 7, 2009
Messages
19,169
a little modification using post #2
Code:
docmd.OpenForm FormName:="frm_NewPurchase", OpenArgs:=Me.cboVendorID
then on the "frm_NewPurchase" form's Load Event:
Code:
Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
    DoCmd.GotoRecord, , acNewRec
    Me.VendorNo = Me.OpenArgs
End If
End Sub
 

zeroaccess

Active member
Local time
Today, 11:26
Joined
Jan 30, 2020
Messages
671
Is acNewRec equal to a WHERE 1=0? Does acNewRec give you a single new record, without the whole recordset loaded into the form?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:26
Joined
Feb 28, 2001
Messages
26,999
Is acNewRec equal to a WHERE 1=0? Does acNewRec give you a single new record, without the whole recordset loaded into the form?

Generally, "NewRec" means exactly what it says - a new record with nothing in it EXCEPT default values (when specified.) Of course, both the tabledef and the form's controls can individually specify default values.
 

zeroaccess

Active member
Local time
Today, 11:26
Joined
Jan 30, 2020
Messages
671
Generally, "NewRec" means exactly what it says - a new record with nothing in it EXCEPT default values (when specified.) Of course, both the tabledef and the form's controls can individually specify default values.
Not being at the computer to try it - but what I'm getting at is can you cycle the records with the navigation controls? 1=0 is a way of preventing that. Think of it like a memory optimization. Next time I'm at the computer I'll just have to try both ways.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:26
Joined
Oct 29, 2018
Messages
21,358
Not being at the computer to try it - but what I'm getting at is can you cycle the records with the navigation controls? 1=0 is a way of preventing that. Think of it like a memory optimization. Next time I'm at the computer I'll just have to try both ways.
When you do get a chance to try it, you might also consider trying the acFormAdd approach as part of your comparison test. Cheers!
 

zeroaccess

Active member
Local time
Today, 11:26
Joined
Jan 30, 2020
Messages
671
When you do get a chance to try it, you might also consider trying the acFormAdd approach as part of your comparison test. Cheers!
Is it right to assume that you would not use a WHERE condition when using this Data Mode?

Actually I'm thinking these are not the same.

AcNewRec is used with DoCmd.GotoRecord, after you have opened a form. So you have already either loaded the recordset or not.

That is different than DoCmd.OpenForm on the click of a button <-- this comes first
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:26
Joined
Oct 29, 2018
Messages
21,358
Is it right to assume that you would not use a WHERE condition when using this Data Mode?
Normally, you won't use a WhereCondition or Filter, but you could test that too and let us know what you find out. I haven't tried it, so I don't know the real answer.
 

zeroaccess

Active member
Local time
Today, 11:26
Joined
Jan 30, 2020
Messages
671
Well I'll tell you what, I just tested OpenForm WHERE 1=0 against OpenForm with no where condition but with the acFormAdd Data Mode, and the results are surprising.

This opens the form with a filter enabled on the navigation bar. You can undo the filter and cycle the records:

Code:
DoCmd.OpenForm "frmInspection", acNormal, , 1 = 0, , acDialog

That is the opposite of the wisdom on the internet that I had previously absorbed, and I guess I never tested it to be sure!

This opens the form with no filter and no records to cycle through. It truly is an empty form:

Code:
DoCmd.OpenForm "frmInspection", acNormal, , , acFormAdd, acDialog

I am going to change my code to acFormAdd as logic would dictate that it is more efficient. In addition, I am going to disable my "New" button on the form when a record is opened by selecting it from the record list (that opens with a filter for that record), so that users can not start a new record using the form with the full recordset loaded. There is no difference in speed on my 4.6 Ghz system loading off of an SSD, but there may be a difference with 10 users and a back end on the network.

Thanks for the tip DBguy!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:26
Joined
Oct 29, 2018
Messages
21,358
Hi. And thanks for letting us know what you discovered. I'm sure it will help others in the future. Cheers!
 

Users who are viewing this thread

Top Bottom