form wizard help

[miken5678]

i tried to search this but wasnt sure on the phrase to search by.

My issue is I have about 20 individual forms each which are unique to a table and those tables unique to one parent table.

I have created a main form which pulls the universal items from the main parent tbl and matches based on if statements to pull the subform(one of the 20 frms/tbls).

I originally created one form linked to one table within the wizard and from there i copied that form and just changed the control source.

The issue i have come across is I am trying to add an item from the main universal table to one of the forms. I went in added a new box and changed the record source on the form to

SELECT tblPRMNew.*, tblMainHeader.EnteredbyMGI FROM tblMainHeader LEFT JOIN tblPRMNew ON tblMainHeader.MainHeaderID = tblPRMNew.MainHeaderID;

Everything is fine until I add a new item and link it to the record source that is within the mainheadertbl.

I have also tried not changing the record source for the form properties and instead just tried adding a new item and changing the control source by doing an expression builder. I come up with
=[tblMainHeader]![EnteredbyMGI]
then once i open the form i get a #name? which i think is stating it isnt linked correctly

in either case once i save the table keep in mind this is access 03 once saved i cannot open the form in design view or the form view and in many cases the saving the form causes access to crash.

So is there a way to reopen the wizard to link a secondary table to an existing form? or what am i doing wrong

thanks

 

[miken5678]

ok i just tried copying a field and changing the record source. I got the #name? listing within the box and then i did a select query for the form. Wow it saves and still opens but still with the #name? sign. Is access hard coded once you use the wizard to that one table?

it seems as if the crashes are random and when saves happens which really ticks me.. not sure if it is just access 03 but usually a machine restart fixes the issue.

 

[miken5678]

GO Figure

the old

SELECT tblPRMNew.*, tblMainHeader.EnteredbyMGI FROM tblMainHeader LEFT JOIN tblPRMNew ON tblMainHeader.MainHeaderID = tblPRMNew.MainHeaderID;


the new (that works)

SELECT [tblCNRCancellation].[CNRCancellationID], [tblCNRCancellation].[MainHeaderID], [tblCNRCancellation].[UWRequest], [tblCNRCancellation].[InsuredRequest], [tblCNRCancellation].[InsuredsSignatureDate], [tblCNRCancellation].[MortgageeForeclosureRequest], [tblCNRCancellation].[ReasonWithSupportingDocumentation], [tblCNRCancellation].[PFCRequest], [tblCNRCancellation].[PFCContract], [tblCNRCancellation].[MIFax], [tblCNRCancellation].[EndorsementPending], [tblCNRCancellation].[OSE], [tblCNRCancellation].[UWRequestComp], [tblCNRCancellation].[InsuredRequestComp], [tblCNRCancellation].[InsuredsSignatureDateComp], [tblCNRCancellation].[MortgageeForeclosureRequestComp], [tblCNRCancellation].[ReasonWithSupportingDocumentationComp], [tblCNRCancellation].[PFCRequestComp], [tblCNRCancellation].[PFCContractComp], [tblCNRCancellation].[MIFaxComp], [tblCNRCancellation].[EndorsementPendingComp], [tblCNRCancellation].[OSEComp], [tblCNRCancellation].[UWRequestUWResponse], [tblCNRCancellation].[InsuredRequestUWResponse], [tblCNRCancellation].[InsuredsSignatureDateUWResponse], [tblCNRCancellation].[MortgageeForeclosureRequestUWResponse], [tblCNRCancellation].[ReasonWithSupportingDocumentationUWResponse], [tblCNRCancellation].[PFCRequestUWResponse], [tblCNRCancellation].[PFCContractUWResponse], [tblCNRCancellation].[MIFaxUWResponse], [tblCNRCancellation].[EndorsementPendingUWResponse], [tblCNRCancellation].[OSEUWResponse], [tblCNRCancellation].[PolicyNumber], [tblCNRCancellation].[EfectiveDateWithinPolicyTerm], [tblCNRCancellation].[MultipleLocationsItems], [tblCNRCancellation].[Losses], [tblCNRCancellation].[CancellationReasonCode], [tblCNRCancellation].[RequiredLegalNotice], [tblCNRCancellation].[FlatCancel], [tblCNRCancellation].[ProRate], [tblCNRCancellation].[ShortRate], [tblCNRCancellation].[SystemRatedPolicyCorrectly], [tblCNRCancellation].[AllRatingInformationInputOntoPolicyRecordCorrectly], [tblCNRCancellation].[UWStamp], [tblCNRCancellation].[F7LogNotes], [tblCNRCancellation].[PolicyNumberComp], [tblCNRCancellation].[EfectiveDateWithinPolicyTermComp], [tblCNRCancellation].[InflationFactorCoverageAmount], [tblCNRCancellation].[MultipleLocationsItemsComp], [tblCNRCancellation].[LossesComp], [tblCNRCancellation].[PaymentPlan], [tblCNRCancellation].[CancellationReasonCodeComp], [tblCNRCancellation].[CorrectPaymentReceived], [tblCNRCancellation].[RequiredLegalNoticeComp], [tblCNRCancellation].[FlatCancelComp], [tblCNRCancellation].[PremiumFinanceCompany], [tblCNRCancellation].[ProRateComp], [tblCNRCancellation].[ShortRateComp], [tblCNRCancellation].[SystemRatedPolicyCorrectlyComp], [tblCNRCancellation].[AllRatingInformationInputOntoPolicyRecordCorrectlyComp], [tblCNRCancellation].[UWStampComp], [tblCNRCancellation].[F7LogNotesComp], [tblCNRCancellation].[PolicyNumberUWResponse], [tblCNRCancellation].[EfectiveDateWithinPolicyTermUWResponse], [tblCNRCancellation].[MultipleLocationsItemsUWResponse], [tblCNRCancellation].[LossesUWResponse], [tblCNRCancellation].[CancellationReasonCodeUWResponse], [tblCNRCancellation].[RequiredLegalNoticeUWResponse], [tblCNRCancellation].[FlatCancelUWResponse], [tblCNRCancellation].[ProRateUWResponse], [tblCNRCancellation].[ShortRateUWResponse], [tblCNRCancellation].[SystemRatedPolicyCorrectlyUWResponse], [tblCNRCancellation].[AllInformationInputontoPolicyRecordCorrectlyUWResponse], [tblCNRCancellation].[UWStampUWResponse], [tblCNRCancellation].[F7LogNotesUWResponse], [tblCNRCancellation].[OutputGenerateCorrectly], [tblCNRCancellation].[CancellationNotice], [tblCNRCancellation].[MailingAddress], [tblCNRCancellation].[CheckPayee], [tblCNRCancellation].[Omniform], [tblCNRCancellation].[OutputGenerateCorrectlyComp], [tblCNRCancellation].[CancellationNoticeComp], [tblCNRCancellation].[MailingAddressComp], [tblCNRCancellation].[CheckPayeeComp], [tblCNRCancellation].[OmniformComp], [tblCNRCancellation].[OutputGenerateCorrectlyUWResponse], [tblCNRCancellation].[CancellationNoticeUWResponse], [tblCNRCancellation].[MailingAddressUWResponse], [tblCNRCancellation].[CheckPayeeUWResponse], [tblCNRCancellation].[OmniformUWResponse], [tblMainHeader].[EnteredbyMGI] FROM ([tblMainHeader] LEFT OUTER JOIN [tblCNRCancellation] ON [tblMainHeader].[MainHeaderID] =[tblCNRCancellation].[MainHeaderID])

ok i built a new form with the wizard and included all the fields that were required from both tables. I checked the recourd source and it is vastly different. Copied over to an existing form and it works.. only thing i can think of is it is a glitch.. you can see the compared code