Solved Building the correct query for a form

[Teri Bridges]

I could use some help
My db has four forms Course>Lesson>Topic>Event.
Course and lesson are linked by Course ID .... 1 to many
Lesson and Topic are linked by Lesson ID .... 1 to many
Topic and event are linked by topic ID .... 1 to many

I have a change form "Course-Change_Sfrm", that users can enter either a course change or an event change. I was hoping to have only one change log (from). My thought here is if it is an event change, the CatalogID would be left blank, and if it is a Catalog change the eventID would be left blank.

The change form has a CatalogID field and an EventsID field.
I would like to click a button on the course form and open the change log where the CatalogID is = to the CatalogID then
I would like to click a button on the events form and have the change form open where the EventID is = to the EventID
I created a select query ....Course table, Lesson table, Topic table, and event Table
I pulled the corresponding ID fields into the query. I then built a form based on this query.

All the IDs populate correctly.
I created a command button on the event form and said …on click open change log if EventID=EventID

DoCmd.OpenForm "Course-Change_Sfrm", , , "EventID =" & Me.EventID

The form opens but the event ID field is blank ,and it is not limiting the return to just the specific eventID’s.

I then tried DoCmd.OpenForm "Course-Change_Sfrm", , , "CatalogID =" & Me.CatalogID and i get a prompt window

Any suggestions are greatly appreciated.

 

[Teri Bridges]

I also tried ... DoCmd.OpenForm "Event-change_Sfrm", , , "CatalogID = " & Me.CatalogID & " AND EventID = " & Me.EventID

 

[Gasman]

CRM101 does not exist or is misspelled.

 

[mike60smart]

I also tried ... DoCmd.OpenForm "Event-change_Sfrm", , , "CatalogID = " & Me.CatalogID & " AND EventID = " & Me.EventID

View attachment 110487

Hi Teri

Your CatalogueID is not Numeric it is a Text Datatype

This "CatalogID = " & Me.CatalogID & "

Needs to be changed to this:

"CatalogID = '" & Me.CatalogID & "'"

 

[GaP42]

Note that the Change Log and Event forms show the CatalogiD is inconsistent! (CRM001 vs CRM-001) - so can't be used to make the link i think