Command button broke after converting from mdb to accdb 1

[RenaG]

Hi,

Working in Access 2007. I'm a fairly new Access user.

I have a command button on a form that is used to preload a table. This button was working fine when the DB was an mdb. Recently I did a save as accdb thinking it would bring the db up to date. I just discovered that the command button doesn't work any more. The embedded Macro associated with the On Click event is gone. I have tried to recreate the button in the accdb version but it doesn't open the macro for me to update with the names of the tables involved.

Questions:
Any suggestions on why this happened in the first place?

Any suggestions on what is going on when I try to create the command button now? And how do I get it to work right?

TIA,
~RLG

 

[kjv1611]

How complex is your database? Just asking to see if it would be worthwhile just recreating the thing.... worse case scenario in a way.. but you'd learn a lot along the way.. Will depend upon how important, how much time you have, etc..

What was your button doing in the original database? If you can figure out what it was doing, try building just a blank form, that button, and use the code there. If you can do it with a new form, then it's likely something wrong with the entire form object, such as corruption.

Just in case it is data corruption, have you tried saving from the old one to the new version again to see what happens?

Also, if you do try again, try doing so on local drives, in case you did this over a network previously.

 

[RenaG]

Hi kjv1611 ~

Thanks for the quick reply!

The db is very complex. I inherited it (in the mdb format) and have put in months of work on it. Plus it is due to go live Sept 1 so starting over is not an option. I agree, I would learn a lot but there just isn't the time and I really don't have the heart to do all again, anyway .

The place where I work, locks down our local drives; I can't even see it in windows explorer so I don't have any choice but to work on the network. However, having said that, I just opened the mdb and did another save as Access 2007 (to a new name, of course). I checked the form with the button described above and it works just fine. That's good except that I have made lots of modifications since converting from the mdb to accdb the first time.

So I have another question. The command button wizard (is suppose to) create an embedded macro. I saw a post on another forum about using the DoCmd.RunSQL and manually inserting into the to-table the data from the from-table.

Any thoughts on the pros and cons of using the RunSQL compared to the generated macro (assuming I can get it to work)?

Warmly,
~RLG

 

[kjv1611]

It used to be that VBA would give better performance. Supposedly as of 2007, I think, performance is now equal. So that is no longer a benefit.

Also, it is now easier to see what a Macro is doing, I think, than it used to be.

Personally, I still prefer VBA. It may just be what I'm used to, since I was determined to start out with that before.

So, really, just go with whatever you're more comfortable with, and gets the job done. There are probably still some things you can't do with a macro, such as setting up custom functions, creating your own data types, etc. But if you don't need them, then a macro should suffice just as well.

Setting up what you're asking in VBA is really straight forward:

Something like this:

Private Sub MyButton_Click()
  Dim strSQL As String

  strSQL = "INSERT INTO Table2 (RecordID ,Title, Color) " & vbCrLf & _
          "SELECT RecordID ,Title ,Color " & vbCrLf & _
          "FROM Table1 " & vbCrLf & _
          "WHERE Weight > 5"

  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.Setwarnings True

That's just a made up example, untested.

Basically, you're building a string variable containing the SQL and running it.

vbCrLf = vb Cariage Return Line Feed - it just makes an easy to understand way of adding in a line. With SQL it is not required, but in case you need to debug, I think it makes it easier to read.

SetWarnings turns the Access messages, such as "This will modify data in a table, are you sure you want to do this?" type of messages.

If you want to, need to, debug your SQL code there in VBA, you can do so by inserting this line:

Debug.Print strSQL

At any point after setting the variable equal to the SQL string. To see it, you'll need to look in the "Immediate Window". If it's not there at the bottom of the code, Press <Ctrl>+<G> on your keyboard while the VB Editor is the active window.

 

[RenaG]

Good morning,

Here is what I created in VBA. The example I had seen (before getting your post) did not add the step of creating a variable and putting the sql stmt into the variable:

DoCmd.RunSQL "INSERT INTO NavigatorInfoBreast " _
    & "(PatientID, EducationResourceID) " _
    & "SELECT Patient.ID, EducationResources.ResourceName " _
    & "FROM (Patient INNER JOIN EducationProgramXref ON " _
    & "Patient.ProgramID = EducationProgramXref.ProgramID) " _
    & "INNER JOIN EducationResources ON " _
    & "EducationProgramXref.EdResourceID = EducationResources.ID " _
    & "WHERE (((Patient.ID)=[Forms]![Patient]![PatID]) AND " _
    & "((EducationProgramXref.ProgramID)=[Patient].[ProgramID]));"

When I ran it, I got this error:

Nurse Navigator Database set 12 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes. (etc)

One field that is missing in the Into list is the ID key field (autonumber) of the NavigatorInfoBreast. Do I need to reference it or will it automatically fill and number sequentially? Any suggestions where do I look to resolve these errors?

Thanks for all your help!

~RLG

 

[kjv1611]

You do not need the ID if it is an autonumber. Actually, if you try to insert into an autonumber field, the query should fail.

Regardless, I see your issue. As the error suggests, you're trying to insert one datatype into another. That won't work - well, sometimes, you can, but definitely not the way you're doing it - probably an oversite, seeing the field names.

The following highlighted do not line-up correctly:

DoCmd.RunSQL "INSERT INTO NavigatorInfoBreast " _
    & "(PatientID, [HIGHLIGHT]EducationResourceID[/HIGHLIGHT]) " _
    & "SELECT Patient.ID, EducationResources.[HIGHLIGHT]ResourceName[/HIGHLIGHT] " _
    & "FROM (Patient INNER JOIN EducationProgramXref ON " _
    & "Patient.ProgramID = EducationProgramXref.ProgramID) " _
    & "INNER JOIN EducationResources ON " _
    & "EducationProgramXref.EdResourceID = EducationResources.ID " _
    & "WHERE (((Patient.ID)=[Forms]![Patient]![PatID]) AND " _
    & "((EducationProgramXref.ProgramID)=[Patient].[ProgramID]));"

Fix that, and it should be working.

The DoCmd.SetWarnings commands will get rid of the messages once you've gotten it all squared away.

It's not required to use a variable, but it just often makes for cleaner looking code. Also, in case you have any changes in the code throughout, the variables make that a lot easier.

 

[RenaG]

You're so smart. That is exactly it! Thanks. I also added a Me.Recalc because the added records didn't show on the form. It works so nicely now.

I don't want to put in the DoCmd.SetWarnings commands because the user is suppose to only click the button once. If they accidently do it again I want them to have a way out of adding those records. That warning message would give them that out. But that is good to know in the future if the circumstances are different.

Thanks for all your help!

Have a blessed day!
~RLG

 

[kjv1611]

Yep, I often have to use one or a combination of the following:

Recalc,Refresh,Requery. Sometimes you can just use one on one of the controls, but if it fits, use it on the whole form.

Oh, trust me, it's not that I'm smart, but I (along with many many others here) have seen the same mistake, and I'll openly admit I've MADE the same mistake.

 

[RenaG]

Ok, then you are wise from experience! :-D

Thank you for all your help.

~RLG