Docmd.openform will not open my form!

[Mmmel]

It's weird - I've used this code all over the place and it has always worked (well, up 'til now!)

I have an instructor FAQs form that has a question and then a button called "See Answer!". When they click the button, another form is supposed to pop up with both the question and the answer. Both forms are based on the same table, which looks like:

Question_ID - an autonumbered primary key
Question - the question
Answer - the answer! : )

In the On_Click event of the button, I have the code:

DoCmd.OpenForm "IKB: Instructor FAQs Display for Instructors", , , "[Question_ID] = " & Question_ID

This should pass the ID of the question (the ID's are correct) and display both the question and the answer. However, NOTHING opens! The form name is spelled correctly, I'm sure of that. What am I missing? Is there something in the form itself that needs to be changed? I've tried comparing every last setting on my other pop-up forms to this one and cannot figure it out.

Any insight would be great! Thanks! (Access 2007)

[Stewart Ross]

Hi. There's no obvious problem with your DoCmd.OpenForm, so you'll need to try a few tests to eliminate possibilities.

First, make sure you don't have an On Error Resume Next in your on-click code - if you do you won't get notification of errors that occur at run-time.

Test 1: Check that the form with the questions and answers opens manually and displays the set of records you would expect it to. Check its name again and make sure it is the same as in your DoCmd.Openform statement. Check also that the Question_ID autonumber field underlying your form is indeed named Question_ID and not Question ID. This is to guard against the automatic substitution of underscores for spaces which is done by the VB editor. Intellisense allows a control named Question ID to be referred to using Me.Question_ID but NOT [Question_ID].

Test 2: In the on-click code remove the Where clause (and all the commas) after the formname in Docmd.Openform. Does the form then open? I would expect it to, because if the form name was not correct an error would be generated that the form could not be found.

Test 3: If the form does open in test 2 then the Where clause is at issue. Add a Where clause back in but instead of referring to the value of Question_ID substitute a known existing question ID (e.g. 15) into the statement:

DoCmd.Openform "IKB: Instructor FAQs Display for Instructors", , , "[Question_ID] = 15"

Does the form then open and display the known question? If it does there is a problem with the question_ID control value passed to the Where clause.

Sorry I can't be more specific with what might be wrong. It is rare for OpenForm to be a problem. I reckon that systematically eliminating potential causes and effects to find the root of this conundrum is the best way forward.

Welcome to Bytes!

-Stewart

[Mmmel]

Thanks for the response, Stewart! I will go through these steps and hopefully figure out what's wrong!

Cheers!

[Mmmel]

Hi again!

OK - I solved the problem and thought I would post the solution up here for anyone else who might be having the same problem. Oh, and you can file the solution under "D" for "duhhhhh... " : )

The code started off as:

DoCmd.OpenForm "IKB: Instructor FAQs Display for Instructors", , , "[Question_ID] = " & Question_ID

I then changed the control names so the code became:

DoCmd.OpenForm "IKB: Instructor FAQs Display for Instructors", , , "[QuestionID] = " & QuestionID

That didn't work, so I simply added square brackets to the last QuestionID like so:

DoCmd.OpenForm "IKB: Instructor FAQs Display for Instructors", , , "[QuestionID] = " & [QuestionID]

I really didn't think I needed them, but after I added them the problem was magically fixed.

Thanks again! Go Canada Go! : )

[Stewart Ross]

Hi Mmmel. You needed the square brackets to tell the VB environment you were referring to a control from the underlying form. If you just put down NameOfSomething, the VB environment will treat this as the name of a VB variable. Without explicit error checking for non-declared variables, VB will simply assign a null to such a variable - resulting in no match for your filter.

If you had the statement Option Explicit On at the top of your code (which you really should be using) the VB editor would have thrown an 'undeclared variable' compile error when you referred to QuestionID without having a DIM statement matching it.

When you use the square brackets you are telling the VB editor you are referring to a control name, or a field within the data source to which the form is bound.

You could have used the name without brackets by qualifying it explicitly using the Me shortcut (which refers to the underlying form's properties)

"[QuestionID]=" & Me.QuestionID

To sum up, controls in the underlying form are properties of the form, and in referring to them you should make this clear, either using the Me shortcut (as in Me.SomeControlN ame) or the full reference to the form concerned if, say, you are referring to a control in a different form (as in Forms("YourForm Name").Somecont rolName).

-Stewart

[Mmmel]

Hi Stewart!

I just saw this reply today - thanks! I figured it had something to do with it not being recognized as a field name, but I did not know why or how to avoid it in the future. So, I just add the line of code:

Option Explicit On

at the top of the code to avoid this in the future? I ask since I'm having a similar problem with another bit of code. I'm trying to loop through a list of email addresses and fire up a new Outlook message. It works in one database, but when I try pretty well the same code in a different database, it just sits there and looks at me blankly. I've checked all the Options, and all the same libraries and things are referenced. I'm going to add the code above and see if it at least complains! If I can't figure it out, can I post the code back here? Thanks again!

[Mmmel]

Option Explicit gives me a syntax error!

Hi again!

Well, I tried putting in the Option Explicit On code. I put it in right below the first line (before any variables were declared) and it gave me a syntax error!

Setting that aside for a minute, can anyone see why this bit of code below does not work? It, once again, will not open anything! This code is behind the On Click event of a button and is supposed to create an email that uses a query to loop through and make the Bcc field. I've checked, and the query is working properly and all the email addresses are there. It just won't make my email!

Anything obviously wrong with this?

Private Sub Command68_Click()
Dim strEMail As String
Dim oOutlook As Object
Dim oMail As Object
Dim strAddr As String
Dim MyDB As DAO.Database
Dim rstEMail As DAO.Recordset
  
Set oOutlook = CreateObject("Outlook.Application")
Set oMail = oOutlook.CreateItem(0)
  
'Retrieve all E-Mail Addressess in Email: Students Fall
Set MyDB = CurrentDb
Set rstEMail = MyDB.OpenRecordset("Select * From [Email: Students Winter]", dbOpenSnapshot, dbOpenForwardOnly)
  
With rstEMail
  Do While Not .EOF
    'Build the Recipients String
    strEMail = strEMail & ![E-mail] & ";"
      .MoveNext
  Loop
End With
'--------------------------------------------------
  
With oMail
  .To = "something@domain.ca"
  .Bcc = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  .Body = ""
  .Subject = ""
    .Display
End With
  
Set oMail = Nothing
Set oOutlook = Nothing
  
rstEMail.Close
Set rstEMail = Nothing
End Sub

Thanks again!

[Mmmel]

I've tried everything. I just CANNOT understand why this works in one database and not another! ARGH........... ....Going to bed now. I give up! : )