Close 'dirty' form without hassle? 1

[Darrylles]

Hi all,

I've been working on this problem for 3-4 days now and would really appreciate a definitive answer (i.e. full code in whichever event(s)) to solve the problem.

There is plenty of help out there e.g.: beforeupdate - if me.newrecord then set me.dirty and cancel = true etc etc etc. With each piece of advice, I'm getting yet another error or warning message.

Currently I'm getting: error 2115 - i.e. I'm preventing Access from saving the record!

I'm not one to give up, and I'm still working on this, but have started this thread in hope that I won't be trying to solve this when I reach a ripe old age.

Here's the scenario:

Unbound frmA has a combo (cmbTopic) which is bound.

I want the user to be able to add a new item to this combo via double-clicking it which shows frmTopic_Add. I can't use the combo 'not-in-list' option because the 'Topic' has various other user fields.

When frmTopic_Add is shown, the user has Topic and Description to complete, but on completion of Topic and leaving the field - I have to check that the Topic entry does not already exist in the table.
If it does, I want to:

1) Inform the user that this is a duplicate entry.
2) Set the CALLING FORM cmbTopic to the attempted entry.
3) Close frmTopic_Add WITHOUT saving this 'dirty' record and without having the user to endure useless warning messages from Access.

If I could give you 10 stars for this solution - I would.

Many thanks in advance.

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[TheAceMan1]

How are ya Darrylles . . .

[blue]I can't use the combo 'not-in-list' option because the 'Topic' has various other user fields.[/blue]

Now thats something! . . . because the [blue]NotInList[/blue] event is exactly what I'm gonna use!

Basically your going to open [blue]frmTopic_Add[/blue] within the event. There's more to it than that, but I don't wanna bog you down with explanation. I'll try to explain a little as I go. BTW . . . this is not complex!

However I need from you the tablename you desire to append to, and the fieldnames of that table you wish to include in the append.

Have no fear the . . . . is here!

See Ya! . . . . . .

 

[danvlas]

Within the NotInList procedure:
Open the form in Dialog mode to pause the NotInList code, maybe set OpenArgs to NewData. Within Form_Load, set the corresponding control to NewData so that user does not have to retype. After all fields are populated and the record is saved, close the form and the combo will behave itself.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Darrylles]

Hi,

Ok Ace:

table = [tblTopic]
fieldnames = [topic_title] & [topic_desc]
The table has an autonumber primary key called [pk]

Thanks in advance,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[TheAceMan1]

Darrylles . . .

I'm working this up now (with explanation) but I'll be in and out all day.

Postback the [blue]RowSource[/blue] of the combo. I just need to be sure of its structure . . .

See Ya! . . . . . .

 

[Darrylles]

Ace....

cmbTopic rowsource =

SELECT [pk], [topic_title], [topic_description] FROM tblTopic;

Thanks again for your help.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[TheAceMan1]

Roger That Darrylles . . .

Just as I thought!. So here we go . . .

In the declaration section of a module in the modules window copy/paste the following Public Variable:

[blue]Public nilAdded As Boolean[/blue]

[blue][tt]****************
* frmA Updates *
****************[/tt][/blue]
[ol][li]In the forms [blue]OnOpen[/blue] event, copy/paste the following:

[blue]   nilAdded = False[/blue]

[/li]
[li]In the [blue]NotInList[/blue] event of the combo, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim NL As String, DL As String, flgNotice As Boolean
   
   NL = vbNewLine
   DL = vbNewLine & vbNewLine
   
   Msg = "The title '" & NewData & "' " & _
         "is not in the dropdown list!" & DL & _
         "Click 'Yes' to add it." & DL & _
         "Click 'No' to abort . . ."
   Style = vbInformation + vbYesNo
   Title = "User Selection Required! . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      DoCmd.OpenForm "frmTopicAdd", , , , , acDialog, NewData
      
      If nilAdded Then
         nilAdded = False
         Response = acDataErrAdded
      Else
         flgNotice = True
      End If
   Else
      flgNotice = True
   End If
    
   If flgNotice Then
      Response = acDataErrContinue
      
      Msg = "You have two options:" & DL & _
            "1) Select an item from the dropdown list." & NL & _
            "     or" & NL & _
            "2) Hit 'Esc' to continue . . ."
      Style = vbInformation + vbOKOnly
      Title = "Operational Notice! . . ."
      MsgBox Msg, Style, Title
   End If[/blue]

[/li][/ol]
[blue][tt]************************
* frmTopic_Add Updates *
************************[/tt][/blue]
[ol][li]Insure the textboxes are [blue]unbound[/blue] even if you have to replace them.[/li]
[li]Set the [blue]Name[/blue] property of the textboxes to match the field names in the table.[/li]
[li] Set the following form properties:
[ol a][li]Record Source [blue]is blank, no entry![/blue][/li]
[li]Auto Resize [blue]Yes[/blue][/li]
[li]Auto Center [blue]Yes[/blue][/li]
[li]Pop Up [blue]Yes[/blue][/li]
[li]Modal [blue]Yes[/blue][/li][/ol][/li]
[li]In the [blue]OnLoad[/blue] event of the form, copy/paste the following:

[blue]   Me!Topic_Title = Me.OpenArgs
   Me!Topic_Desc.SetFocus
   Me!Topic_Title.Enabled = False
   nilAdded = False[/blue]

[/li]
[li]Add a [blue]command button for saving[/blue] and in the [blue]On Click[/blue] event, copy/paste the following:

[blue]   Dim SQL As String
   
   SQL = "INSERT INTO tblTopic (Topic_Title, Topic_Desc) " & _
         "VALUES ('" & Me!Topic_Title & "','" _
                     & Me!Topic_Desc & "');"
                     
   DoCmd.RunSQL SQL
   nilAdded = True
   DoCmd.Close acForm, "frmTopicAdd", acSaveNo[/blue]

[/li][/ol]
[blue]Let me know how ya make out . . .[/blue]

See Ya! . . . . . .

 

[Darrylles]

Ace,

Working on this now. Will be back within the hour.

p.s. You an Unreal Tourney player?

Thnx,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

Ace the name - Ace the answer,

This worked a treat (with some minor tweaking regarding my personal form object naming conventions (txtDescription as opposed to Topic_Desc etc)).

Please make this generic and place it in the FAQ - I'd feel like a thief if I did it, and I feel that it will be extremely useful if put in there.

Many thanks TheAceMan1, I get the feeling that you are almost as obsessed with achieving 'efficiency' as I am, the difference being that you are either very quick on the uptake with MS Access, or I am very slow. (I'm not exactly new to MS Access).

One star from me - anyone who uses this must click the "Thank the AceMan1 for this valuable post!" link to show their appreciation for a very cool, efficient solution.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.