Clearing an Unbound Combo box with Add New Record Button

[bill8922]

The title of this thread sounds like a simple problem, but I have been working on this for hours and cannot figure it out.



I currently have a form with two unbound combo boxes that have a rowsource referencing a query. Once a selection has been made in the first combo box, it filters the results of the second unbound combobox.

In addition, I also have a few bound text boxes that store user input and an add new record button. The issue I am having is clearing the comboboxes after the user hits add new record. The bound textboxes clear immediately and store information properly, but for some reason I cannot get the comboboxes to clear.

Thus far, I can get it to work by using combobox="" if I make a new command button, but I would like to have the add new record button submit the record and then clear the boxes.

It seems I can get it to work using the combobox="" if I do it as the FIRST line in the private sub for the add new record button, but then it clears the data before storing the information.... which, obvioiusly, is not what I want.

If I add it beneath the rest of the code I have, then it doesn't clear. Am I missing something here? my code for the add new record button is below. Any help would be appreciated.

Private Sub SubmitStatus_click()
On Error GoTo Err_SubmitStatus_Click

[User] = fOSUserName()

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec

Exit_SubmitStatus_Click:
Exit Sub

Err_SubmitStatus_Click:
MsgBox Err.Description
Resume Exit_SubmitStatus_Click

DoCmd.RunSQL "INSERT INTO DailyStatusBackend (MasterProjectID)" & "Values(&me.combo14.column(6)&)"
CurrentDb.Execute strSQL, dbFailOnError


'this is where I have been adding the combobox="" and it doesn't work. Neither does me.combobox.value=null, me.combobox="", etc.

End Sub

Thank you again

[ItsMe]

Maybe use the oncurrent event of the form and check the form's NewRecord Property.

if me.newrecord = true
Me.combo.value = ""
end if

https://msdn.microsoft.com/en-us/lib.../ff198278.aspx

[ssanfu]

Private Sub SubmitStatus_click()
On Error GoTo Err_SubmitStatus_Click

[User] = fOSUserName()

'  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'this is a better method to save a record
If Me.Dirty then
   Me.Dirty = FALSE
End If

DoCmd.GoToRecord , , acNewRec


Exit_SubmitStatus_Click:
Exit Sub   ' << Sub Exits here!!

Err_SubmitStatus_Click:
MsgBox Err.Description
Resume Exit_SubmitStatus_Click

  '<<------- NO code below this line will be executed ------->>

'why are these two lines here?????
'DoCmd.RunSQL "INSERT INTO DailyStatusBackend (MasterProjectID)" & "Values(&me.combo14.column(6)&)"
'CurrentDb.Execute strSQL, dbFailOnError


'this is where I have been adding the combobox="" and it doesn't work. Neither does me.combobox.value=null, me.combobox="", etc.

End Sub

1) The code to clear the combo box is in the wrong place.
2) Isn't the combo box name "combo14"?? And Not "combobox"???

AFTER the line "DoCmd.GoToRecord , , acNewRec" try:

DoCmd.GoToRecord , , acNewRec

Me.combo14 = NULL

or

DoCmd.GoToRecord , , acNewRec

Me.combo14 = Empty

[bill8922]

Thank you, ssanfu, Finally got it working. You are correct that one of the comboboxes was named combo14, but I was just using the combobox label as a place holder as I had multiple boxes to clear. I am still learning to program these systems, so your questioning of the two lines of code was really what helped me get this to work. I needed the system to record the primary key of another table and that was the only way, at the time, I could think to do it. After you questioned those lines, I started to rethink how I was making it record that value.

Final code is below. Much cleaner, and most importantly... it works! Thank you again.


Private Sub SubmitStatus_click()
On Error GoTo Err_SubmitStatus_Click


[User] = fOSUserName()
[MasterProjectID] = Me.Combo14.Column(6)


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec

Combo10 = DefaultValue
Combo14 = DefaultValue
Text16 = ""
Text18 = ""
Text20 = ""
Text22 = ""
Text24 = ""
Text38 = ""
Text49 = ""


Exit_SubmitStatus_Click:
Exit Sub


Err_SubmitStatus_Click:
MsgBox Err.Description
Resume Exit_SubmitStatus_Click


End Sub

[ssanfu]

"User" is a reserved word - shouldn't use reserved words as object names
"DefaultValue" is also a reserved word
see http://www.allenbrowne.com/AppIssueBadWord.html


Again, a better method than

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

is

If Me.Dirty Then
  Me.Dirty = False
End If

Or you can use " Me.Dirty = False"

The "DoCmd.DoMenuItem" commands have been depreciated. They still work due to backward compatibility. But the "DoCmd.DoMenuItem" commands will be removed at some point.



If you are referring to controls on a form, I would recommend using the "Me" keyword to distinguish which are control names and which are VBA variables.
Me.Combo10 = DefaultValue
Me.Combo14 = DefaultValue
Me.Text16 = ""

And you really should take the time to rename object names.
What is "combo10"? Compare that to "cboCompanyName".

[ItsMe]

But the "DoCmd.DoMenuItem" commands will be removed at some point.

This causes me to be sad.

[ssanfu]

I am sad & mad because they removed support for dbf files.