Automatic Filling

[Guest]

I have a form that allows a user to add an appointment for a business. If
the business is new and not in the drop down list, the user can hit a command
button to open another form so that their business details can be stored.

Once the details are entered, as you know Access will assign them with a
number (autonumber). The user will then close the form and have to refresh
the appointment screen and select the newly added business from the drop down
box.

Question, is there a way that access will on closing the appointment form,
automatically refresh and include the autonumber that was generated so the
rest of the businesses details appear on the appointment form?

 

[ruralguy via AccessMonster.com]

Rather than a separate button, I would use the NotInList event and everything
you want to happen will happen.
Mastering the Combo Box NotInList Event
http://www.fontstuff.com/access/acctut20.htm

 

[Guest]

Thank you for your response, looking at the NotinList event, I don't think it
is going to help me that much, though I will use this in a few other of my
combo boxes now.

Back to my initial query, when users tab across to the combo box and cannot
find the business in the list (which I guess will happen 80% of the time),
they need to add the business through a different form which is accessed via
the command button, and as previosuly mentioned when closing this form, i
would like the main enquiry form to refresh and include the businesses
details I have just input.

 

[ruralguy via AccessMonster.com]

You can use the NotInList event to open the other form passing it the
"NewData" and then once completed the Response = acDataErrAdded causes the
ComboBox to requery and now find the "New" data and your AfterUpdate event
will be able to locate and move to the new record.

Thank you for your response, looking at the NotinList event, I don't think it
is going to help me that much, though I will use this in a few other of my
combo boxes now.

Back to my initial query, when users tab across to the combo box and cannot
find the business in the list (which I guess will happen 80% of the time),
they need to add the business through a different form which is accessed via
the command button, and as previosuly mentioned when closing this form, i
would like the main enquiry form to refresh and include the businesses
details I have just input.

Rather than a separate button, I would use the NotInList event and everything
you want to happen will happen.

[quoted text clipped - 13 lines]

 

[Guest]

Ruralguy, that is exactly what I need. Could I ask another favour?

Can you type met hrough it, I'm unsure on how to write the code for the this
one....or any other one really!

thanks in advance

You can use the NotInList event to open the other form passing it the
"NewData" and then once completed the Response = acDataErrAdded causes the
ComboBox to requery and now find the "New" data and your AfterUpdate event
will be able to locate and move to the new record.

Thank you for your response, looking at the NotinList event, I don't think it
is going to help me that much, though I will use this in a few other of my
combo boxes now.

Back to my initial query, when users tab across to the combo box and cannot
find the business in the list (which I guess will happen 80% of the time),
they need to add the business through a different form which is accessed via
the command button, and as previosuly mentioned when closing this form, i
would like the main enquiry form to refresh and include the businesses
details I have just input.

Rather than a separate button, I would use the NotInList event and everything
you want to happen will happen.

[quoted text clipped - 13 lines]

automatically refresh and include the autonumber that was generated so the
rest of the businesses details appear on the appointment form?

 

[ruralguy via AccessMonster.com]

Start by implementing Martin Green's NotInList code and then we will change
it so it opens another form and passes the NewData to that form. Post your
NotInList code when you are ready. Also post the name of the other form.

Ruralguy, that is exactly what I need. Could I ask another favour?

Can you type met hrough it, I'm unsure on how to write the code for the this
one....or any other one really!

thanks in advance

You can use the NotInList event to open the other form passing it the
"NewData" and then once completed the Response = acDataErrAdded causes the

[quoted text clipped - 17 lines]

 

[Guest]

I have now typed in the code as below, the other form name is
frmBusinessDetails.

So far the code below doesn't yet work how I thought, because if the
business is not in the drop down list, the user has to type in a number that
doesn't exist, this then prompts the user if they want to add it to the list.


I guess I need the combobox to see if the user does not select anything and
then ask if they want to add a company to the list or should I wait and see
to what you have in mind?

Private Sub cboCompanyID_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Business is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add to Company Details")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblBusiness([CompanyID]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new business ID has been added to the list." _
, vbInformation, "Add to Company List"
Response = acDataErrAdded
Else
MsgBox "Please choose a Business from the list." _
, vbInformation, "Company Details"
Response = acDataErrContinue
End If
cboCompanyID_NotInList_Exit:
Exit Sub
cboCompanyID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCompanyID_NotInList_Exit
End Sub

 

[ruralguy via AccessMonster.com]

Good start. Modifications in line:

I have now typed in the code as below, the other form name is
frmBusinessDetails.

So far the code below doesn't yet work how I thought, because if the
business is not in the drop down list, the user has to type in a number that
doesn't exist, this then prompts the user if they want to add it to the list.

I would think they would type in a COMPANY NAME that is not in the list. Why
a number?

I guess I need the combobox to see if the user does not select anything and
then ask if they want to add a company to the list or should I wait and see
to what you have in mind?

Private Sub cboCompanyID_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The Business is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Add to Company Details")
If intAnswer = vbYes Then

We will replace the next few lines:

strSQL = "INSERT INTO tblBusiness([CompanyID]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new business ID has been added to the list." _
, vbInformation, "Add to Company List"

With:

DoCmd.OpenForm "frmBusinessDetails", , , , , acDialog, NewData

Response = acDataErrAdded
Else
MsgBox "Please choose a Business from the list." _
, vbInformation, "Company Details"
Response = acDataErrContinue
End If
cboCompanyID_NotInList_Exit:
Exit Sub
cboCompanyID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCompanyID_NotInList_Exit
End Sub

That should get your other form open. Now we need to teach the other form
how to look for the OpenArgs.

In the OnLoad event of the frmBusinessDetails form put the following:

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing a New Company Name

'-- USE YOUR CompanyNameControl IN THE NEXT LINE
Me.CompantNameControl = Me.OpenArgs

End Sub

The code in the first form has been halted and is waiting for the user to
complete this form. When you exit the frmBusinessDetails form the ComboBox
will requery and should find the new company and take off from there.