Open form to New Record but with a specific value in text box.

[ddewald]

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]

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"

Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
    Me.VendorNo = Me.OpenArgs
End If
End Sub

 

[ddewald]

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]

Hi. You could use the acFormAdd in the DataMode argument. Also, I would recommend using the DefaultValue property to avoid creating blank records.

 

[ddewald]

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]

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]

It worked great! Thank you both for the help!

 

[theDBguy]

It worked great! Thank you both for the help!

Hi. You're welcome. Arnel and I were happy to assist. Good luck with your project.

 

[ddewald]

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]

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]

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:

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

 

[arnelgp]

a little modification using post #2

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

then on the "frm_NewPurchase" form's Load Event:

Private Sub Form_Load()
If Me.OpenArgs & "" <> "" Then
    DoCmd.GotoRecord, , acNewRec
    Me.VendorNo = Me.OpenArgs
End If
End Sub

 

[zeroaccess]

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]

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]

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]

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]

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]

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]

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:

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:

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]

Hi. And thanks for letting us know what you discovered. I'm sure it will help others in the future. Cheers!