docmd close problem

[colkas]

Hi

I have a combo box which holds client information. When I type ina word notinlist it prompts me to say it is not in the list (as per code below)..

When i go to add the new client and then go back to the quote screen the new customer is not displayed. If I close the quote form completely and then go back in the information is in the combo box...

I tried adding the close command into the code but this just goes in a loop and then ends up with a debug problem...

So how can I change the code so it works and close the form or is there another way to update the combo box from the client table when adding the new record????

If I can close, in the client form perhaps I could put a button to go back to the quote form.... using docmd close and open..... is it possible then when it opens to go to the lastrecord then? Thanks....



Private Sub Combo44_NotInList(NewData As String, Response As Integer)

ans = MsgBox("This account is not on your Clients List. Do you want to add this account ?", vbYesNo, "Asbestos Surveyors Quote Message ?")
If ans = vbNo Then
qclientid = Null
Else

stDocName = "subformclient"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "formquote", acSaveYes

End If
End Sub

 

[John Big Booty]

Welcome to the forum.

Perhaps the code here will do the sort of thing you are after

 

[colkas]

Hi

Sorry that is not quite what I am after and seemsmore complex than my code below (sorry not to up on programming). I have tried to add in the rerquery command but I get a debug error and it does not like the combo44.requery. Any ideas... and thanks


Private Sub Combo44_NotInList(NewData As String, Response As Integer)

ans = MsgBox("This account is not on your Clients List. Do you want to add this account ?", vbYesNo, "Asbestos Surveyors Quote Message ?")

If ans = vbNo Then
qclientid = Null
Else
stDocName = "subformclient"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Combo44.Requery


End If
End Sub

 

[missinglinq]

Try replacing

DoCmd.OpenForm stDocName, , , stLinkCriteria

with

DoCmd.OpenForm stDocName, , , , , acDialog

and see what happens. Access is asynchronous, which means that lines of code are executed, one after the other, without waiting for the previous line of code to finish executing. Your code

DoCmd.OpenForm stDocName, , , stLinkCriteria
Combo44.Requery

will result in the cbo being requeried before you've entered the new data in the secondary form. By adding the acDialog to the command, it tells Access to open the secondary form in Modal which suspends execution of the rest of the code until the user has entered the new data and closed the secondary form.

This may or may not solve the problem you're having, but is absolutely necessary for this code to work.

Linq ;0)>

 

[colkas]

Hi Linq

Please exucse me but I ma not so hot on coding.... I ahve changed my code as I think you suggested but I still get the Vis Bacis Endbug message and the line
Combo44.Requery is highlighted in yellow. My full code below..... Any other ideas or if I misuderstood please put me right.... many thanks

Private Sub Combo44_NotInList(NewData As String, Response As Integer)
ans = MsgBox("This account is not on your Clients List. Do you want to add this account ?", vbYesNo, "Asbestos Surveyors Quote Message ?")

If ans = vbNo Then
qclientid = Null
Else
stDocName = "subformclient"
DoCmd.OpenForm stDocName, , , , , acDialog
Combo44.Requery

'DoCmd.Close acForm, "formquote"
End If
End Sub

 

[missinglinq]

Access has a bad habit of actually hilighting the line after the line causing problems. Are you sure the name of the form you're trying to open is subformclient?

Only other thing I can suggest is that you zip up the file and attach it here. If it's in 2003 or earlier format I'll look at it. If in 2007/2010 format hopefully someone who runs this will take a look at it.

Linq ;0)>
Only thing else I can offer tis for you to

 

[CBrighton]

Add the requery to the on close event of subformclient. This has the bonus that it will never be run before the user inputs the new piece of data which you are requerying to find.

Something like (replace bolded part with the name of the form which the combobox is on):

Forms![B]FormName[/B]!subformclient.requery

If you are still getting errors please provide the whole error message.

 

[colkas]

Hi

I have attached a copy of the DB, I would appreciate any pointers....

I am using the formquote form.
When I enter data into client details that is not in the list it asks me if I want to enter a new client and I reply Yes and this takes me through to the clinet form. Add the new client and new site if needed then go back to the quote screen... Porblems

1. The text I fiorst wrote in si still showing
2. The new client I added does not show in the drop down combo.

And being cheeky if I click No is there a way to blank the clinet details entry

Many Thanks

 

[colkas]

Hi Cbrighton

 

[colkas]

opps sorry posted to soon

Hi Cbrighton

I have added this to the OnClose event on the form subformclient.

Forms!formquote!subformclient.requery

When i try and run the routine OR go into the design of the subforclient I am getting this error message

Microsoft Access cannot find the object 'Forms!formquote!subformclient.requery'


I have checked the form titles and they are fully correct

Thanks

 

[CBrighton]

Sorry, I posted the incorrect code. Now that I have seen the uploaded database I can include the combobox name which I should have put in last time but mistakenly substituted the name of the form you launch in the not in list event...

Forms!formquote!Combo44.requery

 

[colkas]

Hi CBrighton

I out in the new code as suggested in the ONclose for subformclient and it still does not work (the combo box name as changed but shouldnt make a difference)

Forms!formquote!cboQclientid.requery

Same message as above
Microsoft Access cannot find the object 'Forms!formquote!cboQclientid.requery'

Any other ideas I would be grateful
Thanks

 

[boblarson]

See your other post where I responded and gave you the correct Not In List event code and the code which you have here that is wrong and should also have been on the form's load event and not the open event.

 

[colkas]

Great this one is solved thanks for everyones input.... many thanks