95% of the way is not good enough

[nhcaver]

Ok, so continuing the VBA newbie learning curve...I have come to a new impasse. Once again, this is with unbound forms, so in an effort to not waste any of your time those of you who feel the need to debate the sanity of going unbound I kindly request that you help someone who is working with bound forms (I have questioned my sanity enough of late).

Ok, so for those who wanna take a stab at this, here is the situation:

• I have written my first class module (pretty simple and straight forward) and made reference to it in the code behind for the form that holds the referenced objects for the data to be filled in
• I have written another module that, I hope, will connect to my tblMain, select the record that the user puts in the form (since this will be used in multimple forms, once I get this down the first time it *should* be easier).
• I have written the code behind the form.

And here lays the situation...I have debugged, gone through my code...to my newbie eyes (I have been doing this since 5:00 Eastern Time and its now 19:31) there has got to be something I am missing since I keep getting "Type Mismatch Error". So, this seems to be something that would be rooted from my SQL statement. So, if anyone is game, can you point out where I have erred (and please, no comments about, "you should have done this as a bound form", this is a matter of opinion).

the objApplication comes from Form_frmRemove where
Public objApplication As New clsApplication

So, here is the basDatabase:

Option Compare Database
Option Explicit

Dim cnn As ADODB.Connection
Dim rstApplication As ADODB.Recordset

Public Function FindApplication()
  
   Set cnn = New ADODB.Connection
   Set rstApplication = New ADODB.Recordset
  
   Set cnn = CurrentProject.Connection
  
   Dim SQLrcd As String
  
   SQLrcd = "SELECT * from tblMain where AppID = " & Form_frmRemove.objApplication.AppID
  
   With rstApplication
       .LockType = adLockOptimistic
       .CursorType = adOpenKeyset
       .ActiveConnection = cnn
       .Open SQLrcd
  
       If .EOF Then
           Form_frmRemove.objApplication.AppID = 0

       Else

           FillAppObject
           .Close

           .Open "SELECT tblMain.AppID FROM tblMain WHERE AppID = " & Form_frmRemove.objApplication.AppID

           .Update
  
           End If

             .Close

     End With
  
Exit_Here:

    Exit Function

Err_Handler:

    MsgBox err.Description, vbCritical
      Resume Next

End Function

Public Sub FillAppObject()

 With rstApplication
       Form_frmRemove.objApplication.AppID = !AppID
     Form_frmRemove.objApplication.AppLName = !AppLName
     Form_frmRemove.objApplication.AppFName = !AppFName
     Form_frmRemove.objApplication.Criteria1 = !Criteria1
     Form_frmRemove.objApplication.ThirdParty = !ThirdParty
     Form_frmRemove.objApplication.ActionDate = !ActionDate
     Form_frmRemove.objApplication.Notes = !Notes
     Form_frmRemove.objApplication.empID = !empID
     Form_frmRemove.objApplication.empName = !empName

End With

End Sub

Public Sub ClearRecordsetMemory()

    Set rstApplication = Nothing

End Sub

If it helps, here are the data types defined in the tblMain:

AppID long integer
AppLName text
AppFName text
Criteria1 text (this is actually from a lookup in another table in the db)
ThirdParty text (same as the Criteria1, from a lookup)
ActionDate stored as short date, comes from =Date$()
Notes text
empID text
empName stored as text, comes from =fOSGetUser()

Thanks for the help in advance. If you need to see the rest of it, let me know.

 

[Banana]

One crucial piece of information: When you get that Type Mismatch, does it then highlight anything in the code windows?

 

[nhcaver]

As for highlighting in the code window...I just noticed that there might be something fluky with the SQL statement (though it looked ok and then I tested it in the SQL window it seemed fine. I will revisit this though I don't think this would return the "Type mismatch"). As for any other code, nothing is highlighted.

 

[ByteMyzer]

Make certain that the value of Form_frmRemove.objApplication.AppID is not blank.

 

[MarkK]

A type mismatch error will occur at a certain line of code. That line will be highlighted by either the compiler or the debugger. You have provided a lot of other information, but this most important piece--where the error occurs--is missing.
Also, have you considered using bound forms?

 

[boblarson]

What is this referring to?

Form_frmRemove.objApplication.AppID

It looks to me like you are trying to get at a subform as you have one too many items there. Is frmRemove the form? If so, what is objApplication?

 

[boblarson]

Once again, this is with unbound forms, so in an effort to not waste any of your time those of you who feel the need to debate the sanity of going unbound I kindly request that you help someone who is working with bound forms (I have questioned my sanity enough of late).

As was said in the movie "Real Genius" -

Professor - "What Bodie?"

Bodie - "I've been giving myself shock treatments."

Professor - "Up the voltage."

Gotta say that I think that applies to those who want to "strip Access of its manhood" so-to-speak and go unbound. You said, to not say it but I gotta say it anyway. Why go through all of the freakin' hassle? It just seems pointless to throw away almost all of what Access can do for you.

But, it's your pain.

(and please, no comments about, "you should have done this as a bound form", this is a matter of opinion).

Yep, a matter of opinion. I also have an opinion on people who like pain for pain's sake. I think they are crazy. And I think those who swing on the unbound side of Access can be classified as "a couple fries short of a happy meal." But again, that's my opinion and I am free to express it. Of course you are free to ignore it.

 

[SOS]

I very rarely use unbound forms (there has to be a problem that can't be solved with a bound form for me to use one, and those are very rare) as I would rather not have to code if I can use native things in Access. As mentioned, it is one of the benefits of Access (major benefit) because it does the work for you. If you are going to go unbound, you might as well download a copy of VB.NET Express and write a program to do it all there. And, at least you'll have managed code for that.

 

[nhcaver]

Ok guys, I finally got it to work today in my office...the objects on my form were successfully populated. Likely this will be my last posting to this forum as there are others that are better suited to the way that my office uses Access (unbound forms). For those of you who responded and made worthwhile contributions, I thank you. I am sure there are many of you on the other forums as well so we will cross paths again. For those of you who made a point of deciding that my use of unbound forms was an error on my part, please be aware that I had considered the option to use the Access "as is" and would not have approached this forum for help to begin with unless I had to. We should have the ability to do with our data as we please. I realize that there are two schools of thought: use Access in the manner that Microsoft provides for us out of the box, and the other is that our data is ours and not Microsoft's and we should do with it as we please. Once it is determined how someone is using Access (bound or unbound) I ask that you be considerate enough to not look down your noses at those who choose to develop using Access in an unbound manner; first of all it is rude to constantly point out what you determine to be a flaw when in fact there is a valid reason, and secondly it is wasting the time and effort of both the person posting the question and the person answering the question.

 

[boblarson]

...when in fact there is a valid reason...

Well, you never shared it which means to me that you were scared to find out it was NOT a valid reason. Just saying there is, doesn't cut the mustard. And my statement still stands - you should not be developing in Access if you are using only unbound forms. There is no reason. You just stripped it of 95% of what it can do for you and therefore you should just develop in .NET instead which has better security, installations are not subject to reference errors, and you can keep people out of the data much easier.

You came into this with an adversarial position in mind, because you know that it is almost impossible to support your position. And, just so you know - your signature about ON DIRTY - is meaningless because with unbound forms they are NEVER dirty.

And rude it coming in and saying that we are being rude in pointing out the truth when you cannot stand up and be responsible enough to provide your REAL REASONS for doing what you are doing. That is rude. So go away, and go with whoever will tell you what you want to hear. It is fine. We will not notice your absence. But you will find that a lot of us run deep on many forums and your method of dealing with us here will carry over and that is just sad. Oh well, don't let the door hit you on the way out.

 

[Funkyaccess]

Well, you never shared it which means to me that you were scared to find out it was NOT a valid reason. Just saying there is, doesn't cut the mustard. And my statement still stands - you should not be developing in Access if you are using only unbound forms. There is no reason. You just stripped it of 95% of what it can do for you and therefore you should just develop in .NET instead which has better security, installations are not subject to reference errors, and you can keep people out of the data much easier.

You came into this with an adversarial position in mind, because you know that it is almost impossible to support your position. And, just so you know - your signature about ON DIRTY - is meaningless because with unbound forms they are NEVER dirty.

And rude it coming in and saying that we are being rude in pointing out the truth when you cannot stand up and be responsible enough to provide your REAL REASONS for doing what you are doing. That is rude. So go away, and go with whoever will tell you what you want to hear. It is fine. We will not notice your absence. But you will find that a lot of us run deep on many forums and your method of dealing with us here will carry over and that is just sad. Oh well, don't let the door hit you on the way out.

rofl - classic.......

If you a VBA newbie as stated on your opening sentence then how do you know that your way is the right way?

I confess I do not know everything in Access but these guys on this forum know what they talking about and have helped me and many others alike.

Word of advice when joinning/posting......come to the board with an open mind. Your knowledge and experience can only grow.