I need help using DoCmd.OpenForm

[DenisM]

Hello, I hope somebody can give me some help.


I created a system in Access to catalogue my banknotes.
One of the features is to display a country profile from a form (frmCountryProfiles), then view the country's banknotes by selecting "View Banknotes" which opens form (frmSelectedCountriesBanknotesV3), see Attachments A and B.

This is the command used when "View Banknotes is selected: DoCmd.OpenForm "frmSelectedCountriesBanknotesV3", acNormal, , "Country_Code = '" & Me.Country_Code & "'"
So far every thing works fine.

I recently decided to add a Countries Index form, see Attachment C.
The idea is to select a country this would call Country Profiles and display the selected country, then call Selected Country Banknotes as above.
I cannot get this to work.
I have used the following examples to open form (frmCountryProfiles) from form (frmCountriesIndex), see Attachments F and G.
On Attachment G both OpenForm commands do not work, the first example gives a syntax error in string ,the second causes the program to exit and return to This PC > Documents.

If you need more information, I will supply.
Thank you for any help.
Denis

[accesstos]

Hi Denis!

Why you use the form "frmWorldBanknotes" while you want to open the form "frmCountryProfiles"?

Try:

DoCmd.OpenForm "frmCountryProfiles",acNormal,,"Country_Name='Albania'"

DoCmd.OpenForm "frmCountryProfiles",acNormal,,"Country_Name='Bulgaria'"

DoCmd.OpenForm "frmCountryProfiles",acNormal,,"Country_Name='Ireland, Republic of'"

(you have two single quote in the last command, so, that's why you get the error)

Cheers,
John

[DenisM]

Hi Denis!

Why you use the form "frmWorldBanknotes" while you want to open the form "frmCountryProfiles"?

Try:

DoCmd.OpenForm "frmCountryProfiles",acNormal,,"Country_Name='Albania'"

DoCmd.OpenForm "frmCountryProfiles",acNormal,,"Country_Name='Bulgaria'"

DoCmd.OpenForm "frmCountryProfiles",acNormal,,"Country_Name='Ireland, Republic of'"

(you have two single quote in the last command, so, that's why you get the error)

Cheers,
John

Hello John,

Thank you for your response. Unfortunately these do not work, when the command is executed it just exits out of Access.

[moke123]

First thing I noticed, and its usually the first thing I look for, Is that you do not have Option Explicit declared.
(Its a personal thing that drives me crazy.)
You should add it to all your modules and then compile your code.
You can set "Require Variable Declaration" in the VBE, go to Tools>options>editor, and it will automatically add it going forward.

[CJ_London]

perhaps your country profile forms does not include a field called country_name in the recordsource. Might be called something else or might actually be a number because you are using lookups in the table.

[orange]

Denis,

For form frmCountryProfilescheck the recordsource when in design view, and post it.

[DenisM]

Hello,
The recordsource is qryCountryProfiles. What do you mean by post it?
Thanks
Denis.

[orange]

Go to design view of your query; select SQL view; then copy the SQL; and paste it in the thread.

[Gicu]

Do you have a field name Country_Name in qryCountryProfiles? What Orange means is to post a screen shot of your query in design view in here (or the entire SQL statement or even better both).

Cheers,

[June7]

Missing field should not cause Access to shutdown. Should trigger a popup input prompt.

If you want to provide db for analysis, follow instructions at bottom of my post.

[DenisM]

Hello,
SQL as requested.

SELECT tblCountries.Country_Name, tblCountries.Country_Official_Name, tblStatus.Status_Description, tblMonetarySystems.Monetary_System_Name, tblCountries.Country_Succeeded_By, tblCountries.Country_Today_Part_Of, tblCountries.Country_Other_Details, tblFlags.Flag_Image, tblFlags.Flag_Image.FileName, tblStatus.Status_Description, tblCountries.Country_Map, tblCountries.Country_Code
FROM (((tblFlags INNER JOIN ((((tblMonetarySystems INNER JOIN ((tblStatus INNER JOIN (tblCountries INNER JOIN tblCountriesStatusJoin ON tblCountries.[Country_ID] = tblCountriesStatusJoin.[CSJ_Country_Id]) ON tblStatus.[Status_ID] = tblCountriesStatusJoin.[CSJ_Status_ID]) INNER JOIN tblCountriesMonetarySystemsJoin ON tblCountries.Country_ID = tblCountriesMonetarySystemsJoin.CMSJ_Country_ID) ON tblMonetarySystems.Monetary_System_ID = tblCountriesMonetarySystemsJoin.CMSJ_Monetary_Syst em_ID) INNER JOIN (tblIssuers INNER JOIN tblIssues ON tblIssuers.Issuer_ID = tblIssues.Issue_Issuer_ID) ON tblMonetarySystems.Monetary_System_ID = tblIssuers.Issuer_Monetary_System_ID) INNER JOIN tblStatusMonetarySystemsJoin ON (tblMonetarySystems.Monetary_System_ID = tblStatusMonetarySystemsJoin.SMSJ_Monetary_System_ ID) AND (tblStatus.Status_ID = tblStatusMonetarySystemsJoin.SMSJ_Status_ID)) INNER JOIN tblCountriesFlagsJoin ON tblCountries.Country_ID = tblCountriesFlagsJoin.CFJ_Country_ID) ON tblFlags.Flag_ID = tblCountriesFlagsJoin.CFJ_Flag_ID) INNER JOIN tblFlagsMonetarySystemsJoin ON (tblFlags.Flag_ID = tblFlagsMonetarySystemsJoin.FMSJ_Flag_ID) AND (tblMonetarySystems.Monetary_System_ID=tblFlagsMon etarySystemsJoin.[FMSJ_Monetary_System_ID])) INNER JOIN tblStatusFlagsJoin ON (tblFlags.Flag_ID = tblStatusFlagsJoin.SFJ_Flag_ID) AND (tblStatus.Status_ID = tblStatusFlagsJoin.SFJ_Status_ID)) INNER JOIN tblBanknotes ON tblIssues.Issue_ID = tblBanknotes.Banknote_Issue_ID
WHERE (((tblCountries.Country_Region) Not Like "CU"))
ORDER BY tblCountries.Country_Code, tblBanknotes.Banknote_TBB_Pick_Nos;

Thank you

Denis

[Welshgasman]

Jeez, how are you or anyone else meant to read that?

SELECT     tblcountries.country_name,
           tblcountries.country_official_name,
           tblstatus.status_description,
           tblmonetarysystems.monetary_system_name,
           tblcountries.country_succeeded_by,
           tblcountries.country_today_part_of,
           tblcountries.country_other_details,
           tblflags.flag_image,
           tblflags.flag_image.filename,
           tblstatus.status_description,
           tblcountries.country_map,
           tblcountries.country_code
FROM       (((tblflags
INNER JOIN ((((tblmonetarysystems
INNER JOIN ((tblstatus
INNER JOIN (tblcountries
INNER JOIN tblcountriesstatusjoin
ON         tblcountries.[Country_ID] = tblcountriesstatusjoin.[CSJ_Country_Id])
ON         tblstatus.[Status_ID] = tblcountriesstatusjoin.[CSJ_Status_ID])
INNER JOIN tblcountriesmonetarysystemsjoin
ON         tblcountries.country_id = tblcountriesmonetarysystemsjoin.cmsj_country_id)
ON         tblmonetarysystems.monetary_system_id = tblcountriesmonetarysystemsjoin.cmsj_monetary_syst em_id)
INNER JOIN (tblissuers
INNER JOIN tblissues
ON         tblissuers.issuer_id = tblissues.issue_issuer_id)
ON         tblmonetarysystems.monetary_system_id = tblissuers.issuer_monetary_system_id)
INNER JOIN tblstatusmonetarysystemsjoin
ON         (
                      tblmonetarysystems.monetary_system_id = tblstatusmonetarysystemsjoin.smsj_monetary_system_ id)
AND        (
                      tblstatus.status_id = tblstatusmonetarysystemsjoin.smsj_status_id))
INNER JOIN tblcountriesflagsjoin
ON         tblcountries.country_id = tblcountriesflagsjoin.cfj_country_id)
ON         tblflags.flag_id = tblcountriesflagsjoin.cfj_flag_id)
INNER JOIN tblflagsmonetarysystemsjoin
ON         (
                      tblflags.flag_id = tblflagsmonetarysystemsjoin.fmsj_flag_id)
AND        (
                      tblmonetarysystems.monetary_system_id=tblflagsmon etarysystemsjoin.[FMSJ_Monetary_System_ID]))
INNER JOIN tblstatusflagsjoin
ON         (
                      tblflags.flag_id = tblstatusflagsjoin.sfj_flag_id)
AND        (
                      tblstatus.status_id = tblstatusflagsjoin.sfj_status_id))
INNER JOIN tblbanknotes
ON         tblissues.issue_id = tblbanknotes.banknote_issue_id
WHERE      (((
                                            tblcountries.country_region) NOT LIKE "CU"))
ORDER BY   tblcountries.country_code,
           tblbanknotes.banknote_tbb_pick_nos;