Button to delete a couple of fields on a record 1

[superxela]

Hello and thanks in advance,

I'm developing a database to keep track of 5 meals per day. I created a menu with 5 drop-down boxes for every day of the week. I need a button that would allow me to clear (delete) the 5 values(fields) on one day without affecting the rest of the days. The table that is holding the data has the values for every day of that week. Each record represents a person and each field contain a meal selected.

I have learned what I know of Access on my own via books and tutorials. So I'm a beginer. If you need to know I'm using Access 2000 Pro on a Windows ME computer.

Thank you,
Alex Rodriguez

 

[Bry12345]

What follows will replace the existing data with an empty string (not to be confused with a null), which will effectively 'delete' the data in those fields.

Add a command button on your form. Add the following code to the on click event:

- - -
Private Sub cmdClearFields_Click()

On Error GoTo Err_cmdClearFields_Click

Me.cboField1 = ""
Me.cobField1 = ""
'do the above for all the fields you want to clear

Exit_cmdClearFields_Click:
Exit Sub

Err_cmdClearFields_Click:
MsgBox Err.Description
Resume Exit_cmdClearFields_Click



End Sub
- - -

The 'limit to list' property will probably have to be set to no, or your lookup table will have to contain a row with the empty string you are changing the value to. - - - -

Bry

 

[superxela]

Hello

Thanks for the help. However I am getting a Visual Basic error that reads "compile error : method or data member not found."

Here is what I did. I created a small form where by entering the record "ID" it brings forward the value of the five fields I want to clear. This will help the user comfirm the values to be deleted. I created a Command button and entered the code to the on click event. Here is how itlooks:
_________________________________________________
Private Sub cmdClearFields_Click()
On Error GoTo Err_cmdClearFields_Click

Me.cboMonday_Breakfast = ""
Me.cobMonday_Breakfast = ""

Me.cboMonday_Lunch = ""
Me.cobMonday_Lunch = ""

Me.cboMonday_Dinner = ""
Me.cobMonday_Dinner = ""

Me.cboMonday_Snack = ""
Me.cobMonday_Snack = ""

Me.cboMonday_Bar = ""
Me.cobMonday_Bar = ""

'do the above for all the fields you want to clear

Exit_cmdClearFields_Click:
Exit Sub

Err_cmdClearFields_Click:
MsgBox Err.Description
Resume Exit_cmdClearFields_Click

End Sub
_____________________________________________________

The actual name of the field Have a space in between (like "Monday Breakfast&quot which I replaced with an "_"(Monday_Breakfast).

Am I placing the fields name wrong?
Please Help?

Thank you,
Superxela

 

[Bry12345]

I'm a little confused. You started with wanting to clear the values of combo boxes on an existing form, but you're now using another form.

What is the record source of the new form? If that record source is not updateable, this won't work. Is the form bound to the table?

I'm not following what you mean where you replaced a "_" where the field name has a space. Where did you do this replacing?

Also, where are you getting the VB error. What line is the error being generated by? - - - -

Bryan

 

[superxela]

Thanks again Bryan for taking the time to help me. I will try to be more clear.

I made a form called "menu." It has five combo boxes under each day of the week. The first combo box is to choose the "Breakfast" for that client. The second to pick the "Lunch," the next for "dinner", "snack" and finally "bar."
The values seleted for the whole week are stored in a single table called "Client Menu." Each row line represents a client. Each column represents a combo box using the following naming: Monday Breakfast, Monday Lunch, Monday Dinner, Monday Snack, Monday Bar, Tuesday Breakfast, Tuesday Lunch...and so on for the whole week.

If you think I need to inclued the "clear field" botton in the original "Menu" form let me know and I will do so.
However what I did now was create a small form named "Menu editing" Here I enter the Client "ID" and pick the day I want to edit. The form then brings the 5 values for that particular day from the "Client Menu" table. I then created the Command button and entered the code as shown above.

The VB erron is highlighting "cboMonday_Breakfast =" fron the third line of code. Which brings me to my next poing.

As you can see in the code I place an underscore between the day of the week and the time of the meal (Monday_Breakfast). The actual field name is "Monday Breakfast" (with a space in between "Monday" and "Breakfast&quot. I'm not sure if I did the right thing by putting the underscore instead of the space; however I tried removing then but that did not solve the error.

Thank you again,
Superxela

 

[Bry12345]

The error is could possibly be attributed to the code not being able to find your combo control. This issue re the name is not what the field name is, but what the control name is. I'll presume that your control is properly bound to the field, or you wouldn't be seeing values in the form.

In the design view of the form, right click on the first combo control, select properties, and tell me what the name is.

Also, what is the record source of this form? Is it the table or is it a query? - - - -

Bryan

 

[superxela]

Here is the info as it shows on the combo box property window for the first field I want deleted. (This combo box is not the first combo box on the form. The First combo box is for the user to select or enter the client's ID)
_______________________________
Combo box: Monday Breakfast
Tab:All
_______________________________
Name: Monday Breakfast
Control Source: Monday Breakfast
Format:
Decimal Place: Auto
Imput Mask:
Row Source Type: Value List
Row Source: "Egg White w/ Vegg
Column Count: 1
Column Head: No
Column With: 1.35"
Bound Column: 1
List Row: 8
List Width: auto
______________________________end

The small form was created from a Query that segregates the values by days. There is one query that has all the Monday meals, another query has Tuesday meals,etc. I think you are on the right track here. The Values are coming from the Query, which take the values from the "Client Menu" Table.

What do we need to change in the code and will that affect both the table and the query? or will the values only be deleted from the query? I dealy I would like both to be left blank.

Thanks Again,
Superxela

 

[superxela]

The "Limit to List" properties is set to No. Just in case you need to know.
Thank you

 

[Bry12345]

Your code is not properly referencing your control. The code must contain the same name as the control, or Access can't find it, which is likely happening. Try this and then we'll deal with any other errors:

Private Sub cmdClearFields_Click()

On Error GoTo Err_cmdClearFields_Click

Me.Monday Breakfast = ""
Me.Tuesday Breakfast = ""
Me.Wednesday Breakfast = ""
Me.Thursday Breakfast = ""
Me.Friday Breakfast = ""
Me.Saturday Breakfast
Me.Sunday Breakfast
Me.Monday Lunch = ""
Me.Tuesday Lunch = ""
Me.Wednesday Lunch = ""
Me.Thursday Lunch = ""
Me.Friday Lunch = ""
Me.Saturday Lunch = ""
Me.Sunday Lunch = ""
Me.Monday Dinner = ""
Me.Tuesday Dinner = ""
Me.Wednesday Dinner = ""
Me.Thursday Dinner = ""
Me.Friday Dinner = ""
Me.Saturday Dinner = ""
Me.Sunday Dinner = ""
Me.Monday Snack = ""
Me.Tuesday Snack = ""
Me.Wednesday Snack = ""
Me.Thursday Snack = ""
Me.Friday Snack = ""
Me.Saturday Snack = ""
Me.Sunday Snack = ""
Me.Monday Bar = ""
Me.Tuesday Bar = ""
Me.Wednesday Bar = ""
Me.Thursday Bar = ""
Me.Friday Bar = ""
Me.Saturday Bar = ""
Me.Sunday Bar = ""

Exit_cmdClearFields_Click:
Exit Sub

Err_cmdClearFields_Click:
MsgBox Err.Description
Resume Exit_cmdClearFields_Click

End Sub

- - -

You only need one line per control . .the 'cob' in my earlier post was a typo . .it was meant to refer to the next control.

What you are doing here is telling Access to set the value of the control you named to an empty string. The name of the control you code in your sub must match the name of the control on the form, not the name of the underlying field. - - - -

Bryan

 

[superxela]

After playing around for some time with not results. I desided to delete that botton and start from the beginning. I created the command botton again and entered the expression "On Click" as shown below. I finally go Access to reconize the Field names. It needed the underscore "_" between "Monday" and the meal after all
(example: "Monday Breakfast " was entered as "Monday_Breakfast&quot.

Here is how it looks now:
____________________________________________________
Private Sub ClearFields_Click()
On Error GoTo Err_cmdClearFields_Click

Me.Monday_Breakfast = ""
Me.Monday_Lunch = ""
Me.Monday_Dinner = ""
Me.Monday_Snack = ""
Me.Monday_Bar = ""

'do the above for all the fields you want to clear

Exit_cmdClearFields_Click:
Exit Sub

Err_cmdClearFields_Click:
MsgBox Err.Description
Resume Exit_cmdClearFields_Click

End Sub
____________________________________________________


So we made some progress. The new error message now reads:
Filed 'Client Menu.Monday Breakfast' cannot be a Zero length string. (followed by an "OK" botton)

I went back to the "Client Menu" table, to check for the Limit to list, and like I said before it is set to "No"

What should I do next?

Thank you for all the time Bryan,

 

[superxela]

I just found the answer in the Access help files:
__________________________________________________________
Make a Text, Memo, or Hyperlink field accept zero-length strings
1. Open the table that includes the field in Design view.

2. Click the field in which you want to enter a zero-length string.

3. Make sure the AllowZeroLength field property is set to Yes.

Note: Only Text, Memo, and Hyperlink fields can accept zero-length strings.
___________________________________________________________

Thanks for all your help Bryan.
Later
Superxela