"Method 'item' of object 'forms' failed.

[AndyCompanyZ]

I have a form (EventComms1) with a subform (Communcation subform)that when I select an item from the combobox (cmboCommunicationName) should update a date field (CommunicationDueDate) on my subform based on the date on the main form (EventTimeStartDay) but it says the "Method 'item' of object 'forms' failed. Here is the code (which isn't elegant but worked when I applied it in another place so apart from the forms and subform syntax works). I know its something to do with my syntax but I have looked in help and copied what I thought should work:

If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 1 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -7, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 2 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -4, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 3 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -1, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 4 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", -1, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 5 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 7, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 6 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 7, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 7 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 14, [Forms]![EventComms1]![EventTimeStartDay])
End If
If Forms![EventComms1]![Communication subform].Form![cmboCommunicationName].Value = 8 Then
Forms![EventComms1]![Communication subform].Form![CommunicationDueDate] = DateAdd("d", 14, [Forms]![EventComms1]![EventTimeStartDay])
End If

Can anyone see what i'm doing wrong please. Thanks

 

[jzwp22]

If the code is being executed from a control on the subform, you are not referencing the control properly since having this: Forms![EventComms1]![Communication subform].Form![cmboCommunicationName] forces Access to look for a subform in your subform which isn't there. You may want to check out this site that list ways to properly reference controls, properties etc. in forms/subforms.

Additionally, you can use the shorthand notation (me.) to save yourself some typing.


If me.[cmboCommunicationName] = 1 Then
me.[CommunicationDueDate] = DateAdd("d", -7, [Forms]![EventComms1]![EventTimeStartDay])
End If

You can also reference the main form with the me.parent notation

If me.[cmboCommunicationName] = 1 Then
me.[CommunicationDueDate] = DateAdd("d", -7, me.Parent![EventTimeStartDay])
End If


You could also use a SELECT CASE...END SELECT statement in place of the series of IF..THEN..END ID

SELECT CASE me.[cmboCommunicationName]

Case is = 1
me.[CommunicationDueDate] = DateAdd("d", -7, me.Parent![EventTimeStartDay])
Case is = 2
me.[CommunicationDueDate] = DateAdd("d", -4, me.Parent![EventTimeStartDay])
.
.
.
END SELECT

 

[AndyCompanyZ]

Thanks I did have Me. before but I thought that might be the problem so took it out. I'll read that site thanks. I see a case statement is probably better just wanted to get this working.

 

[jzwp22]

Were you able to resolve the issue?

 

[AndyCompanyZ]

I now get the same method failed but on the parent object

 

[AndyCompanyZ]

I turned that part back to [Forms]![EventComms1]![EventTimeStartDay])
but it gives the original error

 

[jzwp22]

Outside of a spelling issue, I cannot think of what else could be wrong. Is it possible to post the DB (with any sensitive data removed)?

(BTW, I only have Access 2003 here at work, so I would not be able to open Access 2007/2010 DBs).

 

[AndyCompanyZ]

Here is a stripped down version I have done as an mdb for Access 2003

 

[AndyCompanyZ]

I am getting an invalid outside procedure which i had before which is why I changed the syntax because I thought it was caused by the subform

 

[jzwp22]

You had some code attached to the main form that was not associated with any event (i.e. the invalid outside procedure). I got rid of that. Also, you had the series of IF THENs related to the on click event of the combo box in the subform. That won't work, it has to be in the after update event of the combo box. I clean that up as well. The amended DB is attached.

I took a quick look at your tables, and I see some issues. I see that you have lookups (list/combo boxes) at the table level. Although Access has this capability, it is generally not a good idea to have them as discussed on this site. (I noticed that the site was down at present, so you may have to access it later). It is best to leave the list/combo boxes for forms.

Also, calculated values are generally not stored, so storing the dates as you are doing in the subform, is generally not a good idea. All you would have to store is the ID value of the communication name. You can still use the same code but the control showing the date would not be bound to the underlying table. It would just be a calculated control. You can actually save yourself some time by storing the factor you use in your code for the number of days in the table with the corresponding communication name.

communicationname
-pkCommNameID primary key, autonumber
-txtCommunicationName
-longDaysAfterEventStart

Now if you need to adjust the number of days since start from one event to another, then you would need to bring in the communication name ID field as well as the #of Days into the subform (i.e. bound controls).

Technically speaking you Event table is not normalized. If an event can have multiple event start dates/times (EventStartTimeDay1, EventStartTimeDay2...), that constitutes a one-to-many relationship. For example, what would happen if your event lasts more than 2 days? You would have to restructure your table and every associated query, form and report.

 

[AndyCompanyZ]

Thanks for looking at it and your comments.
I thought I didn't have any tables with comboboxes as I know the problems that casues but this is more a test of a much bigger db that I have copied the tables from so some parts may not be right.
The event table will only ever need events of at most 2 days so I will keep it as that as I already in the full db claculations and reports based on those but i can see what you mean.
The reason behind the subform and form is for the user to be able to look at the event and allocate different communications (communicationname) for each event. Some events can have no communications some all 8 and so i need the form to show instances of each that will be acted on by the user. The communicationdatedue field I want to be automatically filled in based on the eventtimestartday and a set of criteria (that's in the code : 7 days before the event date etc). I really wanted the information on all the events and the communications to show on one datasheet so the user could go in and look across and act according to the day (they will phone or email delegates as the time approached and after the event) but I have no idea how to achieve that beqaring in mind that there could be 8 different options for each event which is why I thought a subform based onn the eventID showing what needs to be done and also the user needs to be able to change dates if needed. This part of the databse is proving the most challenging because of the amount of options that could be for each event. I tried a junction table between the event table and the communication table but got baffled about how to run a form off this as everytime I entered a field from another table all the records would disappear. This seems to me the best way but is not really what I wanted.

I'm not sure what you mean by the longdaysAfterEvent part that is just a date I need for the user to see how long they need to act on the communication and then they will fill in the communicationsent date when that has been done and alter the communicationstatus accordingly. Shouldn't this date be stored as they will need to see it when the form opens.

Thanks again for taking the time to look.

 

[jzwp22]

I'm not sure what you mean by the longdaysAfterEvent part that is just a date I need for the user to see how long they need to act on the communication and then they will fill in the communicationsent date when that has been done and alter the communicationstatus accordingly. Shouldn't this date be stored as they will need to see it when the form opens.

What I meant was that instead of hard coding the number of days within your code (shown in red). Store that day factor with the corresponding communication name in that table as a field. You can then use it in your code. You would bring it in via the combo box and then reference it in your code. This give the advantage of allowing you to change the value in your table without impacting your code.

If me.[cmboCommunicationName] = 1 Then
me.[CommunicationDueDate] = DateAdd("d", -7, [Forms]![EventComms1]![EventTimeStartDay])
End If

I tried a junction table between the event table and the communication table but got baffled about how to run a form off this as everytime I entered a field from another table all the records would disappear.

Your communications table is a junction table between the event table and the communication name table.

I really wanted the information on all the events and the communications to show on one datasheet so the user could go in and look across and act according to the day (they will phone or email delegates as the time approached and after the event) but I have no idea how to achieve that beqaring in mind that there could be 8 different options for each event

What you would do in this case is to create a query that joins the events, communications and communication names table to show all. Create a datasheet form based on this query. You can have a preliminary form where the user can select a date from a combo box (based on a query that looks at your due dates in your communications table) and then filters the datasheet form based on that selection.

 

[AndyCompanyZ]

Thanks again. I did the datasheet as you suggested using a query and it works just great just need to find out how the user will want the data filtered but that shouldn't be a problem. Thanks so much for you time hope I haven't been too much problem.

 

[jzwp22]

You're welcome. Good luck as you move forward with your database.

 

[AndyCompanyZ]

It all worked fine until I converted it back to 2007 and then I get the "invalid outside procedure" error back and even if I go back to the mdb version it comes back it points to the Me. part of the code.

 

[jzwp22]

Copy the code to a Word document for safe keeping, then delete the code in Access. Rebuild the code in the After Update event of the combo box in the subform

 

[AndyCompanyZ]

Thanks yes that solved it.

 

[jzwp22]

Glad you got it working.