Splitting database causes VBA error in form

[omegaleph]

I inherited a database that was not split (maybe this is why). I'm trying to split it and I ran into a problem. I split a version of the database that is working perfectly and then get an error on one of the forms. Obviously, splitting the database is the cause.

So, the form essentially has a list of "Modules" (not related to VBA ones). When you select the module, it looks up the corresponding name in the table and displays it on the form (and you can then select that module to view). This gives me an error in the split db.

The error is run-time error 3251 "operation is not supported for this type of operation."

The lookup table in question is just two columns: "table_ID" with the Module name (i.e. Module A) and "table_name" with the description (i.e. Mood). I checked and the tables themselves are exactly the same between the split and non-split versions. In fact, I even replaced the table in the split version with the non-split one and it didn't change anything. So, something else went wrong.

The code block in question is below. I've indicated the line that gives an error :

 If IsNull(SCIDList.Value) Then
    Else
        Set recSCIDName = CurrentDb.OpenRecordset("SCID III ListBox Names")
        'This is the index method of searching.  It only works with tables that DO NOT have a primary key
        'and have the index property set to YES with no DUPLICATES
        recSCIDName.Index = "table_ID" 'this line gives error
        recSCIDName.Seek "=", SCIDList.Value
        SCIDName = recSCIDName("table_name")
        recSCIDName.Close
    End If

Hopefully, everything I said is clear. Let me know if it isn't.

 

[pbaldy]

You can either use a DLookup to get the value or use an SQL statement instead of the whole table (which would be more efficient anyway). That would look like:

CurrentDb.OpenRecordset("SELECT table_name FROM [SCID III ListBox Names] WHERE table_ID = " & Me.SCIDList)

 

[DJkarl]

I inherited a database that was not split (maybe this is why). I'm trying to split it and I ran into a problem. I split a version of the database that is working perfectly and then get an error on one of the forms. Obviously, splitting the database is the cause.

So, the form essentially has a list of "Modules" (not related to VBA ones). When you select the module, it looks up the corresponding name in the table and displays it on the form (and you can then select that module to view). This gives me an error in the split db.

The error is run-time error 3251 "operation is not supported for this type of operation."

The lookup table in question is just two columns: "table_ID" with the Module name (i.e. Module A) and "table_name" with the description (i.e. Mood). I checked and the tables themselves are exactly the same between the split and non-split versions. In fact, I even replaced the table in the split version with the non-split one and it didn't change anything. So, something else went wrong.

The code block in question is below. I've indicated the line that gives an error :

 If IsNull(SCIDList.Value) Then
    Else
        Set recSCIDName = CurrentDb.OpenRecordset("SCID III ListBox Names")
        'This is the index method of searching.  It only works with tables that DO NOT have a primary key
        'and have the index property set to YES with no DUPLICATES
        recSCIDName.Index = "table_ID" 'this line gives error
        recSCIDName.Seek "=", SCIDList.Value
        SCIDName = recSCIDName("table_name")
        recSCIDName.Close
    End If

Hopefully, everything I said is clear. Let me know if it isn't.

The seek method can only be used on table type recordsets and linked tables can't be opened as table type recordsets.

 

[omegaleph]

Okay. So what would working code look like? I'm not very experienced at VBA.

If IsNull(SCIDList.Value) Then
    Else
          SCIDName = CurrentDb.OpenRecordset("SELECT table_name FROM [SCID III ListBox Names] WHERE table_ID = " & Me.SCIDList)
      End If

?

 

[pbaldy]

No, you'd get rid of the Index and Seek lines though, and then still this:

Set recSCIDName = CurrentDb.OpenRecordset("SELECT table_name FROM [SCID III ListBox Names] WHERE table_ID = " & Me.SCIDList)
SCIDName = recSCIDName("table_name")
recSCIDName.Close

 

[omegaleph]

Okay thanks. I'll try that. Every time I try to paste that line in, its giving me an error and shutting down... I'll just type it manually, but why would it do that?

 

[omegaleph]

Uhh, I can't even go and edit the code (i.e. delete a line) without it crashing on me...

 

[omegaleph]

First, the crashing seems to be caused by me using 2003 while the Access file is 2000.

I'm editing it on a computer using 2000 now, and when I test the form, its giving me a syntax error, with a missing operator in 'table_name = Module A'

 

[pbaldy]

Try this if the value is text.

Set recSCIDName = CurrentDb.OpenRecordset("SELECT table_name FROM [SCID III ListBox Names] WHERE table_ID = '" & Me.SCIDList & "'")

I've never had the problem of Access shutting down while in the code window. Have you done a compact/repair?

 

[omegaleph]

Thanks, I'll try that.

And yeah, I did Compact/Repair. I think it has something to do with 2003 vs 2000, as using it with a machine running 2000 is fine.

 

[pbaldy]

Using 2003 shouldn't matter, but it sounds like some sort of gremlin has invaded. I might also suggest a decompile, on a copy:

http://www.granite.ab.ca/access/decompile.htm

 

[omegaleph]

Awesome; worked like a charm.

Some other questions... not necessarily VBA so let me know if I should make a new topic elsewhere.
1) Access 2003 gives me warnings about "unsafe expressions not blocked" everytime I open the db. Any way around this (so they stop showing up)?
2) Is there any way to "transfer" a tables properties to another? I have one table that has all the right data and another that has the same fields, but some of those fields have descriptions, validation etc. that the first doesn't. The validation would not have to be retroactively applied (like Access asks you when you change something in a table design). Other than data, there is nothing in the first table not in the second too, so I could "override" the design if that makes sense.

Thanks very much.

 

[omegaleph]

Using 2003 shouldn't matter, but it sounds like some sort of gremlin has invaded. I might also suggest a decompile, on a copy:

http://www.granite.ab.ca/access/decompile.htm

The reason I think it is 2003 is because whether it is the current version or backup versions from April, they work fine in 2000 but not in 2003 (work fine referring to being able to edit code). So if something has gone with wrong with compiled code, it doesn't seem like this should be the case.

 

[pbaldy]

1) I don't have 2003 in front of me, but I think you want Tools/Macro/Security

2) Would just copying the data from one table to the other work? That would be an append query.

 

[omegaleph]

1) I don't have 2003 in front of me, but I think you want Tools/Macro/Security

Thanks, I'll check it out.

2) Would just copying the data from one table to the other work? That would be an append query.

Just for reference, Table 1 is the one with the good data and Table 2 is the one with the good structure. I tried to do this manually, but since the Table 2 has some restrictions, some of Table 1 can't be pasted into Table 2. Is there a way to code this so it ignores these rules? For example, a "Must be 0 or 1" might have been added later and some of these fields are blank.

 

[pbaldy]

I won't say that there's no way to copy the properties, but I can't think of one right off (but I work more with SQL Server tables). Can the restrictions be given defaults? In other words, given your example, could blank entries be given a value of 0 or 1 so they pass the restriction when appended?

 

[omegaleph]

Okay, how about this then. Is there a way for me to temporarily "turn off" any restrictions when pasting a batch of data from one table to another?