PC dll error or vb code error?

[Jen0dorf]

I created a combo box based on a table, Depending on which record is selected I want a specific formto open.



trawling the web I created a module as below - I added a button to the form and in the onclick event selected code builder and typed the code-

Private Sub Search_Click()
Dim strParish As String

If Me.Select_Parish = "Axbridge" Then
  strParish = "frm_Axbridge"
ElseIf Me.Select_Parish = "Backwell" Then
  strParish = "frm_Backwell"
  ElseIf Me.Select_Parish = "Badgworth" Then
  strParish = "frm_Badgworth"
  ElseIf Me.Select_Parish = "Banwell" Then
  strParish = "frm_Banwell"
  ElseIf Me.Select_Parish = "Barrow Gurney" Then
  strParish = "frm_BarrowGurney"
  ElseIf Me.Select_Parish = "Berrow" Then
  strParish = "frm_Berrow"
  ElseIf Me.Select_Parish = "Biddisham" Then
  strParish = "frm_biddisham"
  ElseIf Me.Select_Parish = "Blagdon" Then
  strParish = "frm_Blagdon"
  ElseIf Me.Select_Parish = "Bleadon" Then
  strParish = "frm_Bleadon"
  ElseIf Me.Select_Parish = "Brean" Then
  strParish = "frm_Brean"
  ElseIf Me.Select_Parish = "Brockley" Then
  strParish = "frm_Brockley"
  ElseIf Me.Select_Parish = "Burnham-on-Sea" Then
  strParish = "frm_BurnhamOnSea"
  ElseIf Me.Select_Parish = "Burrington" Then
  strParish = "frm_Burrington"
  ElseIf Me.Select_Parish = "Butcombe" Then
  strParish = "frm_Butcombe"
  ElseIf Me.Select_Parish = "Chapel Allerton" Then
  strParish = "frm_ChapelAllerton"
  ElseIf Me.Select_Parish = "Cheddar" Then
  strParish = "frm_Cheddar"
  ElseIf Me.Select_Parish = "Chelvey" Then
  strParish = "frm_Chelvey"
  ElseIf Me.Select_Parish = "Chew Magna" Then
  strParish = "frm_ChewMagna"
  ElseIf Me.Select_Parish = "Chew Stoke" Then
  strParish = "frm_ChewStoke"
  ElseIf Me.Select_Parish = "Christon" Then
  strParish = "frm_Cheriston"
  ElseIf Me.Select_Parish = "Churchill" Then
  strParish = "frm_Churchill"
  ElseIf Me.Select_Parish = "Clapton in Gordano" Then
  strParish = "frm_ClaptoninGordano"
  ElseIf Me.Select_Parish = "Cleeve" Then
  strParish = "frm_cleeve"
  ElseIf Me.Select_Parish = "Clevedon" Then
  strParish = "frm_Clevedon"
  ElseIf Me.Select_Parish = "Clutton" Then
  strParish = "frm_Clutton"
  ElseIf Me.Select_Parish = "Compton Bishop" Then
  strParish = "frm_ComptonBishop"
  ElseIf Me.Select_Parish = "Compton Martin" Then
  strParish = "frm_ComptonMartin"
  ElseIf Me.Select_Parish = "Congresbury" Then
  strParish = "frm_Congresbury"
  ElseIf Me.Select_Parish = "Dundry" Then
  strParish = "frm_Dundry"
  ElseIf Me.Select_Parish = "East Brent" Then
  strParish = "frm_EastBrent"
  ElseIf Me.Select_Parish = "East Harptree" Then
  strParish = "frm_EastHarptree"
  ElseIf Me.Select_Parish = "Easton in Gordano" Then
  strParish = "frm_EastonInGordano"
  ElseIf Me.Select_Parish = "Flax Bourton" Then
  strParish = "frm_FlaxBourton"
  ElseIf Me.Select_Parish = "Hewish" Then
  strParish = "frm_Hewish"
End If

DoCmd.OpenForm strParish

End Sub

When I run it I get the error

runtime error 2492

the action or method requires a Form Name argument

and when I debug the line

 DoCmd.OpenForm strParish

is in yellow.


A search for error tells me I'm missing DLLs and need to download/re install windows dlls.

This does not ring true so I'm suspecting the message is generic and my code is faulty but thought I'd ask just in case as my pc carried out a large win10 update yesterday

thanks

Ian

[Micron]

Put a debug.print statement just before the line to open the form and check the variable value in the immediate window. There doesn't seem to be a whole lot of info on the error number that helps in your situation.

Your syntax looks correct and the method you use to open the form (passing a variable for the form name) should work, but I think I would have done this one of two other ways. 1) similar coding with a Select Case block (they look neater for a many option situation) or 2) have a table of values for the combo (which you might already have anyway) as field 2, and a column for the form name as field 1. The combo would only show the 2nd field but the event would use the first. On the trigger event, you'd get the form name from the first field. That trigger even would look like

Private Sub SomeButton_Click()
docmd.openform Me.comboName
end sub

You could easily add/remove form names from the list without having to alter the code. The way you have it, it will be more work to add or remove a form reference.

[orange]

Do the forms actually exist?

[Jen0dorf]

Hi

so may thoughts and all as usual valid

I have only created 5 of the forms s thats my next point of call and

I have called the values from a table so I'll look into the other suggestions

thanks one and all

Ian

[CJ_London]

why do you have a different form for each parish? sounds like you have not thought through the design properly

Also you could simplify your code considerably to just

If Me.Select_Parish <>"" then docmd.openform "frm_" & replace(me.select_Parish," ","")

[orange]

Ian,
I agree with Ajax, it seems you have a design issue.
Get a good description of the "business" you are dealing with, build a data model that you can test -before doing a bunch of coding (and/or forms, reports...)- then build your database based on your blueprint.

You may save yourself considerable time and effort by reviewing these free videos.
1
2
3

See Steve's (ssanfu) post here for info.

Good luck

[Micron]

Good points, guys. Sometimes I think I focus too much on a solution to the problem (then again, it could be the Dewars). Don't know why else I didn't think that there probably ought to be one form at most, opened with various recordsets or at least filters applied.

[CJ_London]

to apply filters, look at the where paramater for openform - bing openform for full details

[Jen0dorf]

HI

thanks for the thoughts

I did consider putting it all in one main table but I felt that did raise some issues

At the last count The database would include 30 parishes, these parishes can have up to six churches and each church will have a birth, marriage and death spreadsheet

So doing the guestimate maths 30 x6 x 3 gives me 540 spreadsheets looking at the individual spreadsheets I find the average entries is 600

so my guestimate of record total is 324000.

Is this to large a table for access to handle? and will it slow the searches down?

The table (s) will be searched either by year or name.

I did consider using three tables birth,death and marriage which would reduce the size of the table.

At the moment I'm just at the consideration stage and reviewing options etc

Any thoughts welcome

thanks

Ian

[orange]

Ian,

Where did spreadsheet enter the picture -nothing in your original post?

Did you look at the videos I mentioned?

It seems in general you want to display or report similar information from about 50 parishes where each has about 6 churches. Similar data with some headings/groupings.

I still recommend you build a "test-able" blueprint (data model) and design according to your needs.

I definitely would not start with

I created a combo box based on a table,....

.

[Jen0dorf]

Hi

sorry confused the issue

at the present time individual volunteers visit a church and transcribe the birth, marriage and death records into an excel spreadsheet.

At the moment they have completed around 180 churches with more to come in time.

At the moment they are accessing individual spreadsheets for each churches birth, marriage and death records which is far to long winded.

Hence they want o move all the spreadsheets to a database structure.

So I will have all the spreadsheets which of course I can import into access.

Just getting my head around the best way to do it.

Ian

[ssanfu]

so my guestimate of record total is 324000.

In one table, I have slightly over 2,000,000 (two million) records. That is my largest table. So, yes, Access can handle your records. (34 tables total)

parishes can have up to six churches and each church will have a birth, marriage and death spreadsheet

So maybe start with 3 tables
- parishes (1 to many with churches)
- churches (1 to many with records)
- Life events (birth, marriage and death records) (additional field for Life event - birth, marriage or death)

[Jen0dorf]

Hi

thanks for the clarification

2000000 records should be enough ;-) perhaps fewer tables then

cheers

Ian