A ComboBox Question

[Rani]

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form and updates
information into the related table, closes the data entry form and goes back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Chadlon]

Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz' form,
but you may decide that's to complicated - with all the argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

 

[Ragnar Midtskogen]

You have to requery the combo for the added item to show up.
In the code that added the item you set the Response argument of the
NotInList event proc to acDataErrAdded
This causes the combo's control source to be requeried.

The following example from The Access Web's Web site,
http://www.mvps.org/access/ gives the complete code:

'************ Code Start **********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
'*********** Code End **************

Ragnar

 

[Rani]

Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl all the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?

 

[Rani]

Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl all the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?

 

[Chadlon]

Rani,

Looks like you're maybe working too hard at it - Access is ready to do more
than you think.

In the NotInList event, you call the AddCustFrm - which is fine (although
the syntax as posted looks wrong) - but the value that has been rejected is
not passed to the newly-opened form, so the user would have to enter it
again, which might annoy them. You might consider passing the NotInList
NewData value via OpenArgs into the AddCustFrm.

When in the AddCustFrm, it should not be necessary to at all concern
yourself about the 'calling' form Choze_Frm. Let that form look after
itself, when you close the AddCustFrm.

That can be organised simply by setting the Response arg of the NotInList -
as you try to do but have set it to the wrong option.

With regard to your issues:

1. Surely, the data from CustNameCombo is not intended for the cust_tbl? Is
it not _based_ on cust_tbl and intended to identify which Customer is
related to which Contract? Although you have not said so, I would expect the
CustNameCombo control to be based on the cust_tbl primary key, possibly
supported by a 2nd column of cust code or customer name or similar. Now if
"isn't being saved to the cust_Tbl " was a typo and you mean that the
Customer Id is not being saved to the contract table, then there are several
possible reasons why that might be so - one is that the cbx is _not_ based
on the primary key, another is that the cbx is not bound to the cust_id
field in the contracts table and that no other code is putting the chosen
cust_id in place, and maybe others. Unless the Customer name is the primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the underlying
problem.

2. Your second issue is explained by a couple of coding mistakes. First, the
called form is not (based on posted code) modal and so any file edits would
not have taken place before any requery. Second, the requery that might have
been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the combo-box
to the underlying contracts table. That problem is not addressed in anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub

Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl all the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?

Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz'

form,

but you may decide that's to complicated - with all the argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

goes
back

 

[Rani]

thanks,
I was trying to resolve it in the last 4 days or so and you really saved my
behind.

thanks

Rani,

Looks like you're maybe working too hard at it - Access is ready to do more
than you think.

In the NotInList event, you call the AddCustFrm - which is fine (although
the syntax as posted looks wrong) - but the value that has been rejected is
not passed to the newly-opened form, so the user would have to enter it
again, which might annoy them. You might consider passing the NotInList
NewData value via OpenArgs into the AddCustFrm.

When in the AddCustFrm, it should not be necessary to at all concern
yourself about the 'calling' form Choze_Frm. Let that form look after
itself, when you close the AddCustFrm.

That can be organised simply by setting the Response arg of the NotInList -
as you try to do but have set it to the wrong option.

With regard to your issues:

1. Surely, the data from CustNameCombo is not intended for the cust_tbl? Is
it not _based_ on cust_tbl and intended to identify which Customer is
related to which Contract? Although you have not said so, I would expect the
CustNameCombo control to be based on the cust_tbl primary key, possibly
supported by a 2nd column of cust code or customer name or similar. Now if
"isn't being saved to the cust_Tbl " was a typo and you mean that the
Customer Id is not being saved to the contract table, then there are several
possible reasons why that might be so - one is that the cbx is _not_ based
on the primary key, another is that the cbx is not bound to the cust_id
field in the contracts table and that no other code is putting the chosen
cust_id in place, and maybe others. Unless the Customer name is the primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the underlying
problem.

2. Your second issue is explained by a couple of coding mistakes. First, the
called form is not (based on posted code) modal and so any file edits would
not have taken place before any requery. Second, the requery that might have
been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the combo-box
to the underlying contracts table. That problem is not addressed in anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub

Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl all the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?

Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz'

form,

but you may decide that's to complicated - with all the argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form and updates
information into the related table, closes the data entry form and goes
back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Rani]

with related to the previous question:
I have 2 forms that look alike with one difference between them, there is a
difference in the number of the command button, I'm using one form to add
users not threw the combobox and one form to add users threw the combobox.
how can I use the same form using code to eliminate the appearance of the
desired command buttons ?

Rani,

Looks like you're maybe working too hard at it - Access is ready to do more
than you think.

In the NotInList event, you call the AddCustFrm - which is fine (although
the syntax as posted looks wrong) - but the value that has been rejected is
not passed to the newly-opened form, so the user would have to enter it
again, which might annoy them. You might consider passing the NotInList
NewData value via OpenArgs into the AddCustFrm.

When in the AddCustFrm, it should not be necessary to at all concern
yourself about the 'calling' form Choze_Frm. Let that form look after
itself, when you close the AddCustFrm.

That can be organised simply by setting the Response arg of the NotInList -
as you try to do but have set it to the wrong option.

With regard to your issues:

1. Surely, the data from CustNameCombo is not intended for the cust_tbl? Is
it not _based_ on cust_tbl and intended to identify which Customer is
related to which Contract? Although you have not said so, I would expect the
CustNameCombo control to be based on the cust_tbl primary key, possibly
supported by a 2nd column of cust code or customer name or similar. Now if
"isn't being saved to the cust_Tbl " was a typo and you mean that the
Customer Id is not being saved to the contract table, then there are several
possible reasons why that might be so - one is that the cbx is _not_ based
on the primary key, another is that the cbx is not bound to the cust_id
field in the contracts table and that no other code is putting the chosen
cust_id in place, and maybe others. Unless the Customer name is the primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the underlying
problem.

2. Your second issue is explained by a couple of coding mistakes. First, the
called form is not (based on posted code) modal and so any file edits would
not have taken place before any requery. Second, the requery that might have
been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the combo-box
to the underlying contracts table. That problem is not addressed in anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub

Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl all the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?

Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz'

form,

but you may decide that's to complicated - with all the argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form and updates
information into the related table, closes the data entry form and goes
back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Chadlon]

Bit tricky to answer, since I have no knowledge of the form's mechanics. I'm
also a little bit thrown by the reference to 'user' ... I thought we were
talking about a form to browse/add Customers.

In principle, you should be able to accomplish what you want using only one
form.
We have previously looked at one way of launching the Customer form - as a
side-effect of the NotInList event.
What process you plan to use to open the form from elsewhere I can't tell -
but the possibilities include
- menu selection on custom / popup menu
- command button on another form
- dbl-click on an item in a form/subform
and so on.

It should not matter that much from where you open the form.
The considerations are more likely to be related to
- do you need to browse existing records or merely add new records (e.g.
from a menu)
- do you need to open the form at a particular record (e.g. from a
dbl-click)

Some of these things will require you to ensure that the form is not
presently open, others will not be affected by whether the form is already
open or not. These considerations _only_ apply if you avoid the modal
options. Life for the programmer is more complicated if you do not use modal
forms. Generally, life for the user is quite unpleasant if you use lots of
modal forms, or (as in the present case) sometimes have the form open
modally and other times not. For the time being, to keep life simple, you
can afford to ignore the modal/non-modal inconsistency.

If the previous NotInList suggestions are adopted, then the OpenArgs
property in its simplest form is 'spoken for' - i.e. it is already being
used for one purpose, so it's not available for another use.
But that doesn't cause a problem just now.
- If you want merely to add records, close form if open, open form in
DataEntry mode (using acFormAdd)
- If you want to browse records, open form in normal mode, and do a
pre-cautionary Me.FilterOn = false or DoCmd.ApplyFilter -1 or whatever,
depending on where the code is located and what objects you have ready
access to.
- If you want to open at a particular record, open form in normal mode, but
with the 'where' arg set something like
"[Cust_id]=" & Cstr(Me![Cust_Id])
.... that would be used, for example, in the DblClick event of a subform item

I'm hope some of these comments are on target for your particular
difficulty. Be encouraged that the general answer to your question is "Yes,
you should be able to make do with one form".

CD

with related to the previous question:
I have 2 forms that look alike with one difference between them, there is a
difference in the number of the command button, I'm using one form to add
users not threw the combobox and one form to add users threw the combobox.
how can I use the same form using code to eliminate the appearance of the
desired command buttons ?

Rani,

Looks like you're maybe working too hard at it - Access is ready to do more
than you think.

In the NotInList event, you call the AddCustFrm - which is fine (although
the syntax as posted looks wrong) - but the value that has been rejected is
not passed to the newly-opened form, so the user would have to enter it
again, which might annoy them. You might consider passing the NotInList
NewData value via OpenArgs into the AddCustFrm.

When in the AddCustFrm, it should not be necessary to at all concern
yourself about the 'calling' form Choze_Frm. Let that form look after
itself, when you close the AddCustFrm.

That can be organised simply by setting the Response arg of the NotInList -
as you try to do but have set it to the wrong option.

With regard to your issues:

1. Surely, the data from CustNameCombo is not intended for the cust_tbl? Is
it not _based_ on cust_tbl and intended to identify which Customer is
related to which Contract? Although you have not said so, I would expect the
CustNameCombo control to be based on the cust_tbl primary key, possibly
supported by a 2nd column of cust code or customer name or similar. Now if
"isn't being saved to the cust_Tbl " was a typo and you mean that the
Customer Id is not being saved to the contract table, then there are several
possible reasons why that might be so - one is that the cbx is _not_ based
on the primary key, another is that the cbx is not bound to the cust_id
field in the contracts table and that no other code is putting the chosen
cust_id in place, and maybe others. Unless the Customer name is the primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the underlying
problem.

2. Your second issue is explained by a couple of coding mistakes. First, the
called form is not (based on posted code) modal and so any file edits would
not have taken place before any requery. Second, the requery that might have
been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the combo-box
to the underlying contracts table. That problem is not addressed in anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub

Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl

all
the

rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?





Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz' form,

but you may decide that's to complicated - with all the argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form and
updates
information into the related table, closes the data entry form and goes
back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Rani]

CD
Both forms are doing the same thing, adding new records to the customer
table while if the user opened the form from within the program menu, I
would like to enable him to add more then one record at a time by enabling a
+ command button that does exactly that.
if the user opens the form from within the contract form then he can add
only one record therefore the + btn is disabled.
hope this clarifies things

thanks

Bit tricky to answer, since I have no knowledge of the form's mechanics. I'm
also a little bit thrown by the reference to 'user' ... I thought we were
talking about a form to browse/add Customers.

In principle, you should be able to accomplish what you want using only one
form.
We have previously looked at one way of launching the Customer form - as a
side-effect of the NotInList event.
What process you plan to use to open the form from elsewhere I can't tell -
but the possibilities include
- menu selection on custom / popup menu
- command button on another form
- dbl-click on an item in a form/subform
and so on.

It should not matter that much from where you open the form.
The considerations are more likely to be related to
- do you need to browse existing records or merely add new records (e.g.
from a menu)
- do you need to open the form at a particular record (e.g. from a
dbl-click)

Some of these things will require you to ensure that the form is not
presently open, others will not be affected by whether the form is already
open or not. These considerations _only_ apply if you avoid the modal
options. Life for the programmer is more complicated if you do not use modal
forms. Generally, life for the user is quite unpleasant if you use lots of
modal forms, or (as in the present case) sometimes have the form open
modally and other times not. For the time being, to keep life simple, you
can afford to ignore the modal/non-modal inconsistency.

If the previous NotInList suggestions are adopted, then the OpenArgs
property in its simplest form is 'spoken for' - i.e. it is already being
used for one purpose, so it's not available for another use.
But that doesn't cause a problem just now.
- If you want merely to add records, close form if open, open form in
DataEntry mode (using acFormAdd)
- If you want to browse records, open form in normal mode, and do a
pre-cautionary Me.FilterOn = false or DoCmd.ApplyFilter -1 or whatever,
depending on where the code is located and what objects you have ready
access to.
- If you want to open at a particular record, open form in normal mode, but
with the 'where' arg set something like
"[Cust_id]=" & Cstr(Me![Cust_Id])
... that would be used, for example, in the DblClick event of a subform item

I'm hope some of these comments are on target for your particular
difficulty. Be encouraged that the general answer to your question is "Yes,
you should be able to make do with one form".

CD

with related to the previous question:
I have 2 forms that look alike with one difference between them, there

is

a
difference in the number of the command button, I'm using one form to add
users not threw the combobox and one form to add users threw the combobox.
how can I use the same form using code to eliminate the appearance of the
desired command buttons ?

rejected
is cust_tbl?
Is expect
the

Now

if

"isn't being saved to the cust_Tbl " was a typo and you mean that the
Customer Id is not being saved to the contract table, then there are several
possible reasons why that might be so - one is that the cbx is _not_ based
on the primary key, another is that the cbx is not bound to the cust_id
field in the contracts table and that no other code is putting the chosen
cust_id in place, and maybe others. Unless the Customer name is the primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the underlying
problem.

2. Your second issue is explained by a couple of coding mistakes.

First,
the

called form is not (based on posted code) modal and so any file edits would
not have taken place before any requery. Second, the requery that

might
have

been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the combo-box
to the underlying contracts table. That problem is not addressed in anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub


Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl all
the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the AddCustFrm
the Customer name is being transferred to the CustnameCombo field on the
choze_frm but the combo isn't being requery




Any Ideas ?





Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has
been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz'
form,

but you may decide that's to complicated - with all the argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form and
updates
information into the related table, closes the data entry form and
goes
back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Chadlon]

Hi,

I understand the mechanics of your idea - but not the logic.

If someone has the authority and the information to hand to enter one
customer or 20 customers, then why not let them? When they get fed up or run
out of info, they'll go away.

Having said that, it's not my baby, so you can play the game how you will.

To implement your idea, a gross trigger could be the OpenArgs value.

In the scheme outlined so far, the only route into the form that involves
the use of the OpenArgs property is the NotInList event call. You could
modify the Save button functionality, such that if OpenArgs is non-null then
the save button acts as Save-and-Close.

Would that do it?

CD

(PS: A further refinement would be to remove the + button altogether ... use
the OpenArgs non-null condition to force a 'goto new record' and
disable/render invisible the Add Record + button. You'd need to offer a
Cancel button too, in case the user wished they were anywhere but here.)

CD
Both forms are doing the same thing, adding new records to the customer
table while if the user opened the form from within the program menu, I
would like to enable him to add more then one record at a time by enabling a
+ command button that does exactly that.
if the user opens the form from within the contract form then he can add
only one record therefore the + btn is disabled.
hope this clarifies things

thanks

Bit tricky to answer, since I have no knowledge of the form's mechanics. I'm
also a little bit thrown by the reference to 'user' ... I thought we were
talking about a form to browse/add Customers.

In principle, you should be able to accomplish what you want using only one
form.
We have previously looked at one way of launching the Customer form - as a
side-effect of the NotInList event.
What process you plan to use to open the form from elsewhere I can't tell -
but the possibilities include
- menu selection on custom / popup menu
- command button on another form
- dbl-click on an item in a form/subform
and so on.

It should not matter that much from where you open the form.
The considerations are more likely to be related to
- do you need to browse existing records or merely add new records (e.g.
from a menu)
- do you need to open the form at a particular record (e.g. from a
dbl-click)

Some of these things will require you to ensure that the form is not
presently open, others will not be affected by whether the form is already
open or not. These considerations _only_ apply if you avoid the modal
options. Life for the programmer is more complicated if you do not use modal
forms. Generally, life for the user is quite unpleasant if you use lots of
modal forms, or (as in the present case) sometimes have the form open
modally and other times not. For the time being, to keep life simple, you
can afford to ignore the modal/non-modal inconsistency.

If the previous NotInList suggestions are adopted, then the OpenArgs
property in its simplest form is 'spoken for' - i.e. it is already being
used for one purpose, so it's not available for another use.
But that doesn't cause a problem just now.
- If you want merely to add records, close form if open, open form in
DataEntry mode (using acFormAdd)
- If you want to browse records, open form in normal mode, and do a
pre-cautionary Me.FilterOn = false or DoCmd.ApplyFilter -1 or whatever,
depending on where the code is located and what objects you have ready
access to.
- If you want to open at a particular record, open form in normal mode, but
with the 'where' arg set something like
"[Cust_id]=" & Cstr(Me![Cust_Id])
... that would be used, for example, in the DblClick event of a subform item

I'm hope some of these comments are on target for your particular
difficulty. Be encouraged that the general answer to your question is "Yes,
you should be able to make do with one form".

CD

with related to the previous question:
I have 2 forms that look alike with one difference between them, there

is

a
difference in the number of the command button, I'm using one form to add
users not threw the combobox and one form to add users threw the combobox.
how can I use the same form using code to eliminate the appearance of the
desired command buttons ?

Rani,

Looks like you're maybe working too hard at it - Access is ready to do
more
than you think.

In the NotInList event, you call the AddCustFrm - which is fine (although
the syntax as posted looks wrong) - but the value that has been rejected
is
not passed to the newly-opened form, so the user would have to enter it
again, which might annoy them. You might consider passing the NotInList
NewData value via OpenArgs into the AddCustFrm.

When in the AddCustFrm, it should not be necessary to at all concern
yourself about the 'calling' form Choze_Frm. Let that form look after
itself, when you close the AddCustFrm.

That can be organised simply by setting the Response arg of the
NotInList -
as you try to do but have set it to the wrong option.

With regard to your issues:

1. Surely, the data from CustNameCombo is not intended for the cust_tbl?
Is
it not _based_ on cust_tbl and intended to identify which Customer is
related to which Contract? Although you have not said so, I would expect
the
CustNameCombo control to be based on the cust_tbl primary key, possibly
supported by a 2nd column of cust code or customer name or similar.

Now

if
"isn't being saved to the cust_Tbl " was a typo and you mean that the
Customer Id is not being saved to the contract table, then there are
several
possible reasons why that might be so - one is that the cbx is _not_ based
on the primary key, another is that the cbx is not bound to the cust_id
field in the contracts table and that no other code is putting the chosen
cust_id in place, and maybe others. Unless the Customer name is the
primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the underlying
problem.

2. Your second issue is explained by a couple of coding mistakes. First,
the
called form is not (based on posted code) modal and so any file edits
would
not have taken place before any requery. Second, the requery that might
have
been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the combo-box
to the underlying contracts table. That problem is not addressed in
anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As
Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub


Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box (CustNameCombo)

A not in list event does the following:



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

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save
button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the

cust_Tbl
all

the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the
AddCustFrm
the Customer name is being transferred to the CustnameCombo field

on
the

choze_frm but the combo isn't being requery




Any Ideas ?





Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data has
been
added.
You could perform the requery in the AfterUpdate event of the 'zzzz'
form,

but you may decide that's to complicated - with all the
argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal. So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form and
updates
information into the related table, closes the data entry form and
goes
back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Rani]

Since you've asked I'll tell you,
the logic is simple enough, if the user opened the contract form - he wishes
to initiate a contract
if some information is missing he will put it in, though he will enter only
the information required for the contract he is using. therefore
functionality is a bit different.

as for what you wrote, it really helped.
thanks

Hi,

I understand the mechanics of your idea - but not the logic.

If someone has the authority and the information to hand to enter one
customer or 20 customers, then why not let them? When they get fed up or run
out of info, they'll go away.

Having said that, it's not my baby, so you can play the game how you will.

To implement your idea, a gross trigger could be the OpenArgs value.

In the scheme outlined so far, the only route into the form that involves
the use of the OpenArgs property is the NotInList event call. You could
modify the Save button functionality, such that if OpenArgs is non-null then
the save button acts as Save-and-Close.

Would that do it?

CD

(PS: A further refinement would be to remove the + button altogether ... use
the OpenArgs non-null condition to force a 'goto new record' and
disable/render invisible the Add Record + button. You'd need to offer a
Cancel button too, in case the user wished they were anywhere but here.)

CD
Both forms are doing the same thing, adding new records to the customer
table while if the user opened the form from within the program menu, I
would like to enable him to add more then one record at a time by

enabling

a
+ command button that does exactly that.
if the user opens the form from within the contract form then he can add
only one record therefore the + btn is disabled.
hope this clarifies things

thanks

mechanics.
I'm only
one

as

a

lots

of

modal forms, or (as in the present case) sometimes have the form open
modally and other times not. For the time being, to keep life simple, you
can afford to ignore the modal/non-modal inconsistency.

If the previous NotInList suggestions are adopted, then the OpenArgs
property in its simplest form is 'spoken for' - i.e. it is already being
used for one purpose, so it's not available for another use.
But that doesn't cause a problem just now.
- If you want merely to add records, close form if open, open form in
DataEntry mode (using acFormAdd)
- If you want to browse records, open form in normal mode, and do a
pre-cautionary Me.FilterOn = false or DoCmd.ApplyFilter -1 or whatever,
depending on where the code is located and what objects you have ready
access to.
- If you want to open at a particular record, open form in normal

mode,
but

with the 'where' arg set something like
"[Cust_id]=" & Cstr(Me![Cust_Id])
... that would be used, for example, in the DblClick event of a

subform
item

I'm hope some of these comments are on target for your particular
difficulty. Be encouraged that the general answer to your question is "Yes,
you should be able to make do with one form".

CD

with related to the previous question:
I have 2 forms that look alike with one difference between them,

there
is

a
difference in the number of the command button, I'm using one form

to
add

users not threw the combobox and one form to add users threw the combobox.
how can I use the same form using code to eliminate the appearance

of
the

desired command buttons ?

Rani,

Looks like you're maybe working too hard at it - Access is ready

to

do enter
it similar.
Now

acSaveRecord,

,

acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl
all
the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the
AddCustFrm
the Customer name is being transferred to the CustnameCombo

field

on data
has modal.
So, form
and form
and

 

[Chadlon]

Sorry, I 'mis-spoke' ...

I understand the mechanics _and_ the logic of your idea.
I just don't agree.

To repeat, if the user has the info and the inclination and the authority to
enter 20 customers in one session, why create some artificial program device
that prevents him from doing what he wants.

If there is some system imperative that requires a certain sequence or mode
of action, then we quietly apologise to the user and force it the way it
must be. I don't see that that condition applies here.

If there is no good reason to force the user to do something they might
prefer not to do, then accept the additional coding overhead that might be
required to maximise their freedom of action (subject to the project
budget!). In this particular case, it might actually save you program code.

With this form, not a big issue either way and it is your project ... and
you have enough info on the methods to do it as you choose.

CD

Since you've asked I'll tell you,
the logic is simple enough, if the user opened the contract form - he wishes
to initiate a contract
if some information is missing he will put it in, though he will enter only
the information required for the contract he is using. therefore
functionality is a bit different.

as for what you wrote, it really helped.
thanks

Hi,

I understand the mechanics of your idea - but not the logic.

If someone has the authority and the information to hand to enter one
customer or 20 customers, then why not let them? When they get fed up or run
out of info, they'll go away.

Having said that, it's not my baby, so you can play the game how you will.

To implement your idea, a gross trigger could be the OpenArgs value.

In the scheme outlined so far, the only route into the form that involves
the use of the OpenArgs property is the NotInList event call. You could
modify the Save button functionality, such that if OpenArgs is non-null then
the save button acts as Save-and-Close.

Would that do it?

CD

(PS: A further refinement would be to remove the + button altogether ... use
the OpenArgs non-null condition to force a 'goto new record' and
disable/render invisible the Add Record + button. You'd need to offer a
Cancel button too, in case the user wished they were anywhere but here.)



enabling

form -
as

a

side-effect of the NotInList event.
What process you plan to use to open the form from elsewhere I can't
tell -
but the possibilities include
- menu selection on custom / popup menu
- command button on another form
- dbl-click on an item in a form/subform
and so on.

It should not matter that much from where you open the form.
The considerations are more likely to be related to
- do you need to browse existing records or merely add new records (e.g.
from a menu)
- do you need to open the form at a particular record (e.g. from a
dbl-click)

Some of these things will require you to ensure that the form is not
presently open, others will not be affected by whether the form is already
open or not. These considerations _only_ apply if you avoid the modal
options. Life for the programmer is more complicated if you do not use
modal
forms. Generally, life for the user is quite unpleasant if you use

lots

of
modal forms, or (as in the present case) sometimes have the form open
modally and other times not. For the time being, to keep life

simple,
you

can afford to ignore the modal/non-modal inconsistency.

If the previous NotInList suggestions are adopted, then the OpenArgs
property in its simplest form is 'spoken for' - i.e. it is already being
used for one purpose, so it's not available for another use.
But that doesn't cause a problem just now.
- If you want merely to add records, close form if open, open form in
DataEntry mode (using acFormAdd)
- If you want to browse records, open form in normal mode, and do a
pre-cautionary Me.FilterOn = false or DoCmd.ApplyFilter -1 or whatever,
depending on where the code is located and what objects you have ready
access to.
- If you want to open at a particular record, open form in normal mode,
but
with the 'where' arg set something like
"[Cust_id]=" & Cstr(Me![Cust_Id])
... that would be used, for example, in the DblClick event of a subform
item

I'm hope some of these comments are on target for your particular
difficulty. Be encouraged that the general answer to your question is
"Yes,
you should be able to make do with one form".

CD

with related to the previous question:
I have 2 forms that look alike with one difference between them, there
is
a
difference in the number of the command button, I'm using one form to
add
users not threw the combobox and one form to add users threw the
combobox.
how can I use the same form using code to eliminate the appearance of
the
desired command buttons ?

Rani,

Looks like you're maybe working too hard at it - Access is ready

to

do
more
than you think.

In the NotInList event, you call the AddCustFrm - which is fine
(although
the syntax as posted looks wrong) - but the value that has been
rejected
is
not passed to the newly-opened form, so the user would have to enter
it
again, which might annoy them. You might consider passing the
NotInList
NewData value via OpenArgs into the AddCustFrm.

When in the AddCustFrm, it should not be necessary to at all concern
yourself about the 'calling' form Choze_Frm. Let that form look after
itself, when you close the AddCustFrm.

That can be organised simply by setting the Response arg of the
NotInList -
as you try to do but have set it to the wrong option.

With regard to your issues:

1. Surely, the data from CstNameCombo is not intended for the
cust_tbl?
Is
it not _based_ on cust_tbl and intended to identify which

Customer
is

related to which Contract? Although you have not said so, I would
expect
the
CustNameCombo control to be based on the cust_tbl primary key,
possibly
supported by a 2nd column of cust code or customer name or similar.
Now
if
"isn't being saved to the cust_Tbl " was a typo and you mean

that
the

Customer Id is not being saved to the contract table, then there are
several
possible reasons why that might be so - one is that the cbx is _not_
based
on the primary key, another is that the cbx is not bound to the
cust_id
field in the contracts table and that no other code is putting the
chosen
cust_id in place, and maybe others. Unless the Customer name is the
primary
key (g.forbid) then your code line
Me.CustNameCombo = NewData
has got to be a bit of a concern, and might go to explain the
underlying
problem.

2. Your second issue is explained by a couple of coding mistakes.
First,
the
called form is not (based on posted code) modal and so any file edits
would
not have taken place before any requery. Second, the requery that
might
have
been invoked by the Response arg isn't, because it's set to the wrong
option.


Revised coding scheme is outlined below.
It remains for you to get happy about the way you have bound the
combo-box
to the underlying contracts table. That problem is not addressed in
anything
below.

CD

' Choze_Frm
Private Sub CustNameCombo_NotInList(NewData As String, Response As
Integer)
' ensure that "AddCustFrm" is closed, else acDialog and OpenArgs
won't
bite
On local error resume next
Docmd.close acform,"AddCustFrm"
DoCmd.OpenForm "AddCustFrm", , , , ,acDialog,NewData
Response = acDataErrAdded
End Sub

'AddCustFrm
Private Sub Form_Open(Cancel as integer)
' Bring in any start-up value for Customer name (from
CustNameCombo)
If "" <> Nz(Me.OpenArgs,"") Then
Me.CustName.DefaultValue = "=""" & Me.OpenArgs & """"
End If
End Sub

Private Sub SE_Btn_Click()
Me.Dirty = False ' force a save of the current record
' all the other stuff to do with Choze_Frm ... forget it
End Sub


Ok here is the issue

I have a contract (Choze_Frm) on wich I have a combo box
(CustNameCombo)

A not in list event does the following:



Private Sub CustNameCombo_NotInList(NewData As String,

Response

As

Integer)

Me.CustNameCombo = NewData

DoCmd.Minimize

DoCmd.OpenForm "AddCustFrm", acNormal, acDialog

Response = acDataErrContinue

End Sub



At the AddCustFrm (based on a table called Cust_TBL) I have a save
button
that do the following:

Private Sub SE_Btn_Click()

On Error GoTo Err_SE_Btn_Click

Dim ctl As Control

Set ctl = Forms!Choze_Frm!CustNameCombo

DoCmd.Save

DoCmd.Minimize

' DoCmd.DoMenuItem acFormBar, acRecordsMenu,

acSaveRecord,

,
acMenuVer70

DoCmd.OpenForm "Choze_Frm"

DoCmd.GoToControl ctl.Name

DoCmd.Requery

Forms![Choze_Frm]![CustNameCombo] = ""

Forms![Choze_Frm]![CustNameCombo] =
Forms![AddCustFrm]![Shem_Cust]

Exit_SE_Btn_Click:

Exit Sub

Err_SE_Btn_Click:

MsgBox Err.Description

Resume Exit_SE_Btn_Click



End Sub



Here are the issues:

1.. The data from CustnameCombo isn't being saved to the cust_Tbl
all
the
rest of the information is being saved.
2.. after updating the fields in the cust_tbl and closing the
AddCustFrm
the Customer name is being transferred to the CustnameCombo

field

on
the
choze_frm but the combo isn't being requery




Any Ideas ?





Hi Rani,

The 'add to master list' form that you open ... is that modal?
If not, then your DoCmd.Requery may not bite.

For example, if you have
Private Sub xxx_NotInList
DoCmd.OpenForm zzzz
Me.xxx.Requery
etc

then the Requery of the combobox takes place before the new data
has
been
added.
You could perform the requery in the AfterUpdate event of the
'zzzz'
form,

but you may decide that's to complicated - with all the
argument-passing
involved.

Simplest (for now) route is to make the call to 'zzzz' be modal.
So,
DoCmd.OpenForm zzzz,,,,, acDialog
Me.xxx.Requery
will do the job.

The downside is a slight cramping of style in the user interface.

CD

Sorry guys for posting again

my question is:

I have a combobox - while not on list event opens another form
and
updates
information into the related table, closes the data entry form
and
goes
back
to the combobox form,
the information isn't appearing in the combobox.

I tried to run DoCmd.Requery but got no result out of it.

any idea ?

 

[Guest]

I've found that using REFRESH just before you close or open the next form works very well