By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Help with controls on a form

P: n/a
I have two tables 1.tblmonth which holds two fields txtmonth and txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform based
on tblmain, the two forms are also linked on the same fields.

The data entered into txtmonth could be "30/09/04" and the txtqtrlabel data
would be "September 2004" Both the controls are comboboxes with NotInList
events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_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 Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."

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

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

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub

The code for txtqtrlabel is the same apart from the field references.

PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains
the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the
date to appear once. So there should be no duplicates in tblmonth but the
field txtqtrlabel can appear more than once in the tblmain.

Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list, hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Tony, to solve the problem I would recommend a structure change. The month
label (i.e. September 2004) can be computed from the date, it doesn't need
to be stored. You could make the computation in a query feeding each form,
if desired, and link on that calculated field. To fill the Row Source of the
combo box so that it doesn't contain duplicate values, you would also use a
query, create the value using a calculated field, and set the query to only
show unique values.

Example:
SELECT DISTINCT Format([Field2],"mmmm yyyy") AS MonthYear,
DatePart("yyyy",[Field2]) AS Expr1, DatePart("m",[Field2]) AS Expr2
FROM Table1
ORDER BY DatePart("yyyy",[Field2]), DatePart("m",[Field2]);

The two calculated fields Expr1 and Expr2 are included solely for sorting.
The format function will return a text value and so it won't sort as
expected (i.e. chronologically), it will sort in alphabetic order. The last
two fields return numeric values for the year and month and so will sort
correctly.

The DISTINCT keyword is what removes the duplicates. [Field2] is a Date/Time
field type.

--
Wayne Morgan
Microsoft Access MVP
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:co**********@titan.btinternet.com...
I have two tables 1.tblmonth which holds two fields txtmonth and
txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform
based
on tblmain, the two forms are also linked on the same fields.

The data entered into txtmonth could be "30/09/04" and the txtqtrlabel
data
would be "September 2004" Both the controls are comboboxes with NotInList
events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_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 Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."

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

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

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub

The code for txtqtrlabel is the same apart from the field references.

PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get
a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains
the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the
date to appear once. So there should be no duplicates in tblmonth but the
field txtqtrlabel can appear more than once in the tblmain.

Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list,
hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams

Nov 13 '05 #2

P: n/a
Thanks Wayne. Can I deal with the first part the calculated field. I tried
using Format to calculate the txtqtrlabel Control but as a calculated field
it was not stored. This seemed to cause me problems with creating reports
because the two databases were linked on those fields as the calculated
field wasn't stored there was no link with the records.
How do I overcome this if I want to write reports which is based on a query
on both tables when there is no data to link them with?
Sorry if the answer is obvious!!
Thanks
Tony
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:R3******************@newssvr33.news.prodigy.c om...
Tony, to solve the problem I would recommend a structure change. The month
label (i.e. September 2004) can be computed from the date, it doesn't need
to be stored. You could make the computation in a query feeding each form,
if desired, and link on that calculated field. To fill the Row Source of the combo box so that it doesn't contain duplicate values, you would also use a query, create the value using a calculated field, and set the query to only show unique values.

Example:
SELECT DISTINCT Format([Field2],"mmmm yyyy") AS MonthYear,
DatePart("yyyy",[Field2]) AS Expr1, DatePart("m",[Field2]) AS Expr2
FROM Table1
ORDER BY DatePart("yyyy",[Field2]), DatePart("m",[Field2]);

The two calculated fields Expr1 and Expr2 are included solely for sorting.
The format function will return a text value and so it won't sort as
expected (i.e. chronologically), it will sort in alphabetic order. The last two fields return numeric values for the year and month and so will sort
correctly.

The DISTINCT keyword is what removes the duplicates. [Field2] is a Date/Time field type.

--
Wayne Morgan
Microsoft Access MVP
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:co**********@titan.btinternet.com...
I have two tables 1.tblmonth which holds two fields txtmonth and
txtqtrlabel
and 2. tblmain which holds a number of fields but in particular a field
called txtqtrlabel2. The two tables are linked on txtqtrlabel and
txtqtrlabel2. I have a main form based on tblmonth which has a subform
based
on tblmain, the two forms are also linked on the same fields.

The data entered into txtmonth could be "30/09/04" and the txtqtrlabel
data
would be "September 2004" Both the controls are comboboxes with NotInList events. The code for the NotinList event for txtmonth is:
Private Sub cmbmonth_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 Quarter." & vbCrLf
strMsg = strMsg & " Do you want to add the new Quarter to the current
List?" & vbCrLf
strMsg = strMsg & " Click Yes to Add or No to re-type it."

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

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

End If
End If
Set db = Nothing
Set rs = Nothing
End Sub

The code for txtqtrlabel is the same apart from the field references.

PROBLEM - When entering a new record I enter the txtmonth first then the
txtqtrlabel but as soon as I tab out of the main form to the subform I get a
long message which says I cant make the changes I requested because they
would create duplicate values and I should change the data which contains the duplicate data. None of the fields in tblmain are restricted to
duplicates but the field txtmonth is because in that table I only want the date to appear once. So there should be no duplicates in tblmonth but the field txtqtrlabel can appear more than once in the tblmain.

Can anyone help here? I want the user to be able to add dates to the
txtmonth control as they go along by adding any new ones to the list,
hence
the code.
Sorry this is so long!!!!
TIA
Tony Williams


Nov 13 '05 #3

P: n/a
You're correct, the calculated field isn't going to be stored. To work
around this for your report, create a query on the table and add the
calculated field to the query. Now, create the query for the report, using
the query with the calculated field as one of the tables instead of the
table itself. You can now create the join in the query on the table from the
other database and the calculated field.

--
Wayne Morgan
Microsoft Access MVP
"Tony Williams" <tw@tcpinvalid.com> wrote in message
news:co**********@hercules.btinternet.com...
Thanks Wayne. Can I deal with the first part the calculated field. I tried
using Format to calculate the txtqtrlabel Control but as a calculated
field
it was not stored. This seemed to cause me problems with creating reports
because the two databases were linked on those fields as the calculated
field wasn't stored there was no link with the records.
How do I overcome this if I want to write reports which is based on a
query
on both tables when there is no data to link them with?

Nov 13 '05 #4

P: n/a
Hello,
Please excuse me if this is old hat to all. We are in the process of
migrating an older Access database to Access 2002. It makes extensive use
of Macros and in many places it uses the "FormIsLoaded" for several logic
checks.
It appears that formisloaded does not work in access 2002, or we are not
using it correctly for this version of access.
Also, at least for now, we would prefer to stay with the macro
approach rather than take on the task of re-writing them in Visual Basic. I
know, that is what we will have to do eventually.
Can someone assist?
Rich
ri**@hocking.net

Nov 13 '05 #5

P: n/a
I suspect that FormIsLoaded is a user defined VBA function that is being
called by the macro. If that is the case, it probably uses DAO. Access 2000
and newer use ADO by default. Some of the components of these two data
access methods have the same name, such as both have recordsets. The use of
these is determined by the References that are set in Tools|References in
the VBA editor. Open this window and see if there is a check mark next to
"Microsoft DAO 3.6 Object Library". If there isn't then check it and click
Ok. If there is a check mark next to an earlier version of DAO, uncheck it
and check the new one and click Ok.

Next, go into the function and look for statements such as

Dim rst As Recordset, db As Database
Dim qdf As QueryDef

They may be defined on one line (as in the first line above) or on multiple
lines. These 3 are probably the most common DAO items you will find. Change
the Dim statements to

Dim rst As DAO.Recordset, db As DAO.Database
Dim qdf As DAO.QueryDef

Specifying DAO will prevent Access from accidentally trying to use ADO
instead. You will need to do this for all of the VBA code in the database.

Other common DAO items you may find are Field and TableDef. There are more.

--
Wayne Morgan
MS Access MVP
"News" <ri**@hocking.net> wrote in message
news:c6*************@news.ee.net...
Hello,
Please excuse me if this is old hat to all. We are in the process of
migrating an older Access database to Access 2002. It makes extensive use
of Macros and in many places it uses the "FormIsLoaded" for several logic
checks.
It appears that formisloaded does not work in access 2002, or we are
not
using it correctly for this version of access.
Also, at least for now, we would prefer to stay with the macro
approach rather than take on the task of re-writing them in Visual Basic.
I
know, that is what we will have to do eventually.
Can someone assist?
Rich
ri**@hocking.net

Nov 13 '05 #6

P: n/a
Wayne,
Thanks for the response. I should have stated that we are converting an
Access 2.0 database that we have used for many years to a Access2002. Under
Access 2.0, FormIsLoaded was a built-in Condition that could be used in a
Macro. We selected Access2002 because it is the newest Access that still
uses Macros. I have never actually tried to do anything with VBA, but even
if I did, it appears to me that you would have to put it into a RunCode
Action and there does not appear to be anyplace to put it into a Condition.
Perhaps there is a different condition that is in 2002 that I simply
have not found.
How would you set a Condition in a Macro to return a True/False to
check if a Form Is Loaded ?
Thanks.
Rich
Nov 13 '05 #7

P: n/a
Access 2003 still has macros also, but requires Windows 2000, XP, or 2003
Server to run. I don't see the FormIsLoaded as an option in the newer
macros. There is a VBA option to make the test, but the macro won't accept a
value back from the function (i.e. True or False) so I don't know that it
would do you any good unless you have the macro pass a parameter to the
function to tell the function what to do if the form is open.

--
Wayne Morgan
Microsoft Access MVP
"News" <ri**@hocking.net.invalid> wrote in message
news:tm*************@news.ee.net...
Wayne,
Thanks for the response. I should have stated that we are converting
an
Access 2.0 database that we have used for many years to a Access2002.
Under
Access 2.0, FormIsLoaded was a built-in Condition that could be used in a
Macro. We selected Access2002 because it is the newest Access that still
uses Macros. I have never actually tried to do anything with VBA, but
even
if I did, it appears to me that you would have to put it into a RunCode
Action and there does not appear to be anyplace to put it into a
Condition.
Perhaps there is a different condition that is in 2002 that I
simply
have not found.
How would you set a Condition in a Macro to return a True/False
to
check if a Form Is Loaded ?
Thanks.
Rich

Nov 13 '05 #8

P: n/a
The Northwind database has a module named Utility Functions (I think) that
includes a function IsLoaded(), which takes the name of a form as a string,
e.g. IsLoaded("frmSwitchboard") returns True if the form is loaded and false
if not. If you copy this module into your database, you can use the
IsLoaded() function as the condition for macro execution.

To test this, create a macro with the single action BEEP and then in the
Condition column, type

IsLoaded("frmSwitchboard")

or whatever other form name you choose. If the form is loaded and you run
the macro you should get the beep, otherwise not.

The Access 2003 version of the function is this:

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function

It has been available in Northwind for all versions of Access that I can
remember, but the code varies from version to version, so it's best to use
the sample code from the version you have installed.

"News" <ri**@hocking.net> wrote in message
news:c6*************@news.ee.net...
Hello,
Please excuse me if this is old hat to all. We are in the process of
migrating an older Access database to Access 2002. It makes extensive use
of Macros and in many places it uses the "FormIsLoaded" for several logic
checks.
It appears that formisloaded does not work in access 2002, or we are not using it correctly for this version of access.
Also, at least for now, we would prefer to stay with the macro
approach rather than take on the task of re-writing them in Visual Basic. I know, that is what we will have to do eventually.
Can someone assist?
Rich
ri**@hocking.net

Nov 13 '05 #9

P: n/a
I don't know if you want to try this, but there is an option on the tools
menu under Macro to Convert Macros to VBA. Make a backup copy of the file
first.

--
Wayne Morgan
Microsoft Access MVP
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.