How to have control boxes default blank, without data entry property set to yes (1 Viewer)

kvnd · Oct 10, 2018

I have a data entry form which uses vba to loop over some stuff and enters multiple records. This works, except for one problem. It generates an extra record (appearing first, before the others) where the list box field looped over is blank.

This is fixed by setting the data entry property of the form to "No", however, when I do this, upon opening the form all the combo boxes are filled with an existing record. I do not want this, I want all combo boxes to be blank when I open the form. How can I do this?

Minty · Oct 10, 2018

Can you not simply test for the listbox value being null, and skip that phantom record creation?

kvnd · Oct 10, 2018

Minty said:
Can you not simply test for the listbox value being null, and skip that phantom record creation?

Are you sure it is recognizing a null listbox item, even if some are chosen?

Here is my code, I am new to VBA so not sure how to write this.
Add If Not IsNull(varItem) after the "For Each" statement? That doesn't work. Wrong syntax?

Private Sub Submit_Click()
Dim ctl As Control
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Table", dbOpenDynaset, dbAppendOnly)
Set ctl = Me.DrillType
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs![Drill Type] = ctl.ItemData(varItem)
rs!ProgramID = Me.cboProgramD
rs!Day = Me.Day
rs![Drill Format] = Me.DrillFormat
rs.Update
Next varItem
MsgBox "Record Submitted.", vbInformation, "Message"
End Sub

GinaWhipp · Oct 10, 2018

Well, I do see one problem Day is a Reserved Word so you need to bracket that.

As to your issue, are you saying one of the values selected is a blank and you don't want that added to your table?

kvnd · Oct 10, 2018

GinaWhipp said:
Well, I do see one problem Day is a Reserved Word so you need to bracket that.

As to your issue, are you saying one of the values selected is a blank and you don't want that added to your table?

Really? The day data inputs fine from the form.

Anyway, I've taken a picture and described it in MS paint, attached to this comment here, which explains the problem. I do not want that blank first row to be added, only n rows for n selected options in the "Drill Type" list box (in my image, 3 are selected).

I would like any way to solve this. Preferably by just forcing the combo boxes to be empty upon form open, I guess, but I don't know how to do this. If the combo boxes can be forced empty upon opening the form, it means I can disable the data entry form property (which fixes the data entry issue, but causes the combo boxes to display info from an already existing record upon open)

GinaWhipp · Oct 10, 2018

Yes, really AND while it will work just fine it can cause strange things to happen.

The combo box should not coming into the picture UNLESS DrillType is the combo box and not the list box which from the image it does not appear to be. So something strange is happening here because the code clearly goes after the selected values.

Can you upload a sample file?

June7 · Oct 10, 2018

The table is named Table? Advise something more meaningful. Also, advise no spaces in names.

I created table and form. I get an error when I try to name textbox Day. Day is a reserved word. Don't use reserved words as names for anything. I used name tbxDay.

Tested your code and only change was textbox name for tbxDay and declaring variable varItem. (You should have Option Explicit in every module header). Code ran perfectly even when DataEntry set to Yes - no blank record.

Assume input controls are UNBOUND.

If you want to provide db for analysis, follow instructions at bottom of my post.

kvnd · Oct 11, 2018

@June I unbound the boxes and renamed the Day box and now it works. Thanks

gemma-the-husky · Oct 11, 2018

it might be looping round because your tab order is not natural.

It may be writing a record at a point which doesn't correspond to the logical record end. That would probably produce the effect you are describing.

How to have control boxes default blank, without data entry property set to yes (1 Viewer)

kvnd

Registered User.

Minty

AWF VIP

kvnd

Registered User.

GinaWhipp

AWF VIP

kvnd

Registered User.

Attachments

GinaWhipp

AWF VIP

June7

AWF VIP

kvnd

Registered User.

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread