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_NotInL ist(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 = acDataErrContin ue
Else
Set db = CurrentDb
Set rs = db.OpenRecordse t("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContin ue
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
9  2300 
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.btinte rnet.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_NotInL ist(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 = acDataErrContin ue
Else
Set db = CurrentDb
Set rs = db.OpenRecordse t("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContin ue
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
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******** **********@news svr33.news.prod igy.com... 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.btinte rnet.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_NotInL ist(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 = acDataErrContin ue
Else
Set db = CurrentDb
Set rs = db.OpenRecordse t("tblMonth", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!txtMonth = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContin ue
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
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.bti nternet.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?
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 "FormIsLoad ed" 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.ne t
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|Reference s 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.n et> wrote in message
news:c6******** *****@news.ee.n et... 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 "FormIsLoad ed" 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.ne t
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
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.n et.invalid> wrote in message
news:tm******** *****@news.ee.n et... 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
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("frmSw itchboard") 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("frmSw itchboard")
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(strFor mName)
If oAccessObject.I sLoaded Then
If oAccessObject.C urrentView <> 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.n et> wrote in message
news:c6******** *****@news.ee.n et... 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 "FormIsLoad ed" 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.ne t
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: mark |
last post by:
I've been working on an Access 2000 database for a couple of weeks now. I
took a course in access about a year ago, a crash course, and I learned a
ton, but I didn't touch Access for the year since then so I forgot
everything I learned : ( So this little project I envisioned has turned out
to be much harder than I thought. But I think I'm...
|
by: Jason |
last post by:
Hi,
I was wondering if any could point me to an example or give me ideas on how
to dynamically create a form based on a database table? So, I would have a
table designed to tell my application to create certain textboxes, labels,
and combo boxes? Any ideas would be appreciated.
Thanks
|
by: MyNameIsnt |
last post by:
Can anyone tell me why, when I click on the buttons it register 2
characters on the display?
if you use the right mousebutton it works ok, but the buttons dont
flash??
it works fine without the aqua buttons, (using just windows forms
buttons...
Main Form cs file...
|
by: MFC |
last post by:
Ok, after three C# books, (C# How to Program, Programming in the Key
of C#, and C# Weekend Crash Course) and three weeks, I believe I have
tried everything to make a certain form function correctly. I am
trying to learn C# after playing around for a bit with procedural
programming with PHP, not OOP, and believe I have learned quite a bit
in...
|
by: Daniel |
last post by:
I'm new to .Net and all of its abilities so I hope this makes sense.
Basically I'm confused on when is the appropriate time to use web forms
controls vs. regular HTML.
For example in ASP (non-.Net) if I wanted to fill a list it may look
something like this:
-------START CODE
<%
| | |
by: Chad |
last post by:
I have a problem that I am desperate to understand.
It involves dynamically adding controls to a Table control that is built as a result of performing a database query.
I am not looking to avoid the problem by avoiding the table control or resorting to databound controls that better manage state for me. I hope to understand how to solve the...
|
by: Tim::.. |
last post by:
Can someone please help....
I'm having major issues with a user control I'm tring to create!
I an trying to execute a sub called UploadData() from a user control which I
managed to do but for some reason I keep getting the error:
Exception Details: System.NullReferenceException: Object reference not set
to an instance of an object.
|
by: Edwinah63 |
last post by:
Hi everyone,
i was wondering if anyone else had observed this and if there is any
workaround or patch.
i have some screens (an mdi child forms) with the buttons &Add, &Edit,
&Save and E&xit, the idea being that the user can use Alt+A, Alt+E etc
etc
when any screen loads, Add, Edit and Exit are enabled.
|
by: Michael D. Reed |
last post by:
I am using the help class to display a simple help file. I generated the
help file using Word and saving it as a single page Web page (.mht
extension). I show the help file with the following statement.
Help.ShowHelp(Parent:=Me, url:=Me.HELP_URL_PRE & Me.myWorker.HelpFile)
How do I get it to go away when the program exits? Now when I quit...
|
by: =?Utf-8?B?U2l2?= |
last post by:
I have a form that I programmatically generate some check boxes and labels on.
Later on when I want to draw the form with different data I want to clear
the previously created items and then put some new ones on.
In my code I am doing the following:
For Each ctrl In tpMain.Controls
If TypeOf (ctrl) Is CheckBox Then
If...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
| | |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
