Newbie - Need coding Help

[cthies]

I am an access user that normally just uses the functionality of the software without any custom scripting or VBA usage. I was asked by a friend to create a simple database that tracks employee concerns which I did. However, there are three combo boxes that have names and other data supplied from separate tables. When I finish each record and move to the next one, the names used in the previous record automatically populate in the new records combo boxes.

I am looking to CLEAR THE COMBO BOX for the new record. I came across this bit of code along with the following directions which i followed:

______

Private Sub Form_Current()
On Error GoTo ErrorHandler


Me![cboname of box]= Null


ErrorHandlerExit


Exit Sub







ErrorHandler:


MsgBox "Error No: " & Err.Number &": Description: " & Err.Description


Resume ErrorHandlerExit


End Sub

_____________

To clear the entry for entering the next record, follow these steps:


1.Open the form in Design view.
2.Click the Form properties button at the top left of the form.
3.In the Event tab, click the On Current property box and select Event Procedure
4.Click the Build button.
5.Enter the following code at the prompt (SEE CODE ABOVE)
6.Press [Alt]+Q

_________________

i followed this exactly and now I am getting error.....I do not know VBA and am learning as I go.

The FOURTH line of code has "ErrorHandlerExit"

When I run the code I get the error: "Compile Error: Sub or Function Not Defined"

ALSO, am I supposed to leave the Me![cboname of box]= Null expression as is or do I add my own data regarding my database and and tables?

I have searched all over for the solution and have not found any relevant posts. Hoping the experts here can find time to hopefully fix this seemingly easy issue.

Thank you in advance, if any assisting experts are in the Philadelphia area I will buy the beer!!! :banghead:

 

[pbaldy]

You need a colon here:

ErrorHandlerExit:

And the name of your combo box goes there.

 

[cthies]

THANK YOU...

NoW I got a new Error, Code now looks as listed below. I added table name but the bolded and italic part now has error "COMPILE ERROR: LABEL NOT DEFINED"

Also, since I have THREE combo boxes that need to be clear on start of new record, do I add three "= Null" arguments, i.e.:

Me![Liasions] = Null
Me![Doctors] = Null
Me![Hospitals] = Null

THANK YOU!!!!
__________

Private Sub Form_Current()
On Error GoTo ErrorHandler


Me![Liasions] = Null

ErrorHandlerExit:


End Sub



ErrorHandler:


MsgBox "Error No: " & Err.Number & ": Description: " & Err.Description


Resume ErrorHandlerExit


End Sub

 

[pbaldy]

you have 2 End Sub lines. The first should be Exit Sub. Yes, you can put all 3 lines to clear 3 combos.

 

[AccessBlaster]

You could try:

Private Sub Form_Current()
 On Error GoTo Form_Err
  
Me![Liasions] = ""
Me![Doctors] = ""
Me![Hospitals] = ""
   
 Form_Exit:
    Exit Sub
  
 Form_Err:
    MsgBox err.Description
    Resume Form_Exit
  
 End Sub

 

[cthies]

ACCESS BLASTER:

SOOOOOO, close

Code is now as follows:

____

Private Sub Form_Current()
On Error GoTo Form_Err

Me![Liasions] = ""
Me![Physicians] = ""
Me![Hospitals] = ""

Form_Exit:
Exit Sub

Form_Err:
MsgBox Err.Description
Resume Form_Exit

End Sub

______

No errors and When I start a new record I still have values in the comboboxes as before instaed of BLANK combo boxes. The values are whatever the previous record contained.

I DO also get an error in the form that reads: "Microsoft Access can't find the field 'LIasions' referred to in your expression."

Liasions is a table with the names of different employees. There are TWO fields in this field named 'Last Name' and 'First Name'

So I wonder if the code is looking for a specific field in a different table or if it can not find the Liasions table and I need to specify a table name. Maybe:

[Liasions].[Last Name]

Again, I am learning VB and this is a stumper for me :banghead:

 

[AccessBlaster]

Probably need a refresh or requery, not sure how you're "On Current" event will handle it. If you type Me.liasions the intellisence will provide the correct field name. Not sure you need the bang ! and the brackets []

 Private Sub Form_Current()
 On Error GoTo Form_Err
  
Me![Liasions] = ""
Me![Doctors] = ""
Me![Hospitals] = ""
Me.requery   

  Form_Exit:
    Exit Sub
  
 Form_Err:
    MsgBox err.Description
    Resume Form_Exit
  
 End Sub

 

[cthies]

Well, The system is accepting the code you suggested and the Debug is not finding any issues. Now, for unknown Reasons I am getting the error:

"Microsoft Access can't find the field 'Liasions' referred to in your expression.

The form is still populating the combo box fields when a NEW record is started so as of now it is not working. I'm tying to find another way but for now this coding attempt is all I have.

Any continued ideas?

 

[cthies]

OK, Fixed all ERRORS and the below code has no issues BUT the fields STILL DO NOT start out empty in a NEW Record.

Code is as identified below:

___________

Private Sub Form_Current()
On Error GoTo Form_Err

Me![LastNameLis] = ""
Me.Refresh


Form_Exit:
Exit Sub

Form_Err:
MsgBox Err.Description
Resume Form_Exit

End Sub

______________

I changed the name of the field to 'LastNameLis' which is what I am actually trying to have a NULL response when a new field is opened. I am still getting the prior record value in the new records....GRRRRRR

:banghead:

 

[AccessBlaster]

I am still getting the prior record value in the new records....GRRRRRR

This would open a form to a new record.

Private Sub Form_Load()
   DoCmd.GoToRecord , , acNewRec
End Sub

 

[cthies]

AccessBlaster

Your iNput has been MOST educational. The code for the ComboBox seems to work, Now I have an issue that i can PULL DOWN the combo box and the values are present but I can not click on any of them and make them populate the field. The field remains blank.

I currently have TWO tables: [Test 1] and [Liasions]

[Test 1] is the primary table with all fields for the form/database

[Liasions] has only the lastname.firstname of the employee. (Smith.Joe)

The field where these names are keot is named 'LastNameLis' and there is a corresponding field in the [Test 1] table with the same name.

Those field have a connected relationship.

In the properties for the combobox in questions the following settings are in place:

CONTROL SOURCE: =[Liasions]![LastNameLis]

ROW SOURCE: SELECT [Liasions].[LastNameLis], [Liasions].[LastNameLis] FROM Liasions;

ROW SOURCE: Table/Query

BOUND COLUMN: 1

***********UPDATE****************

I remove the CONTROL SOURCE and I can select records from the combo box drop down but IT AGAIN auto populates the next record....AND.....I just found out that if I go backward in the records the combobox selection I made changes all the prior records. SO, IT seems the field is NOT SAVING THE RECORD.....DOUBLE GRRRRRR

 

[jdraw]

Not sure if this is relevant, but Liasions is spelled Liaisons (if it is meant to be the English word). Reality is you can name your tables whatever you like and spelling is anything you want.
Good luck with your project