I am having difficulty creating code that will compare a date in one table (or query) in the [Expire] field and subtracting two months from the date and automatically placing the newly calculated date into the table (or query) in the [Notify Expiration] second field. For example:
Please note that for the month of January and February, the subtraction is minus two for these two months and the subtraction is minus one for the year. All other subsequent months just subtracts two for the months leaving the year intact.
I would appreciate any help that will progmatically accomplish these calculations.
Hi, I’m having a problem creating a form for easy data entry. Let me give you a little background. I have a database for a tutor program, students request a tutor for a class, and then I fill out a follow up report to find out if they are being tutored or changed their minds. Next I enter the contact information indicating the date and time of their tutoring session.
Students can be tutored in more than one class but they can only have one tutor for each class.
I have a form where I enter the tutor request info and sub forms for their classes, follow ups, and sessions. Ideally I would also like a form just to enter the session info. I want a combo box to select the tutor and then a sub form to select the tutee with a combo box which will auto fill the Subject, and Course fields, and field to enter the date and contact hrs.
I think the problem is with my relationships. I’m just kind of stuck. I think I need to create a form based on a query but I haven't done that before. Any help would be appreciated. Attached is a screen shot of my relationships and of my current form for your reference.
I have a simple database that has in the same record in a table 3 fields of last names that are populated by entering the data in a form. There could be the same last names in each of the 3 fields. (ie; John Doe, Mary Doe, Little Doe) My problem is that I want to make a report by clicking on a button searching by the last name (Doe) and consider each of the 3 last names in my search criteria. Does anyone know how this can be accomplished as not to miss any of the (Doe's in this case)? Please help if you can I know it sounds confusing.
I am pullling data from a query using an unbound form and a query that that uses linked tables. I can not edit the index of the tables, so is there a way i can create a new index for sorting data in a form?
I have a little problem in creating a msg box that then activate a code. How can I do? I was doing
Private Sub Chiusura_Pratica_Click() MsgBox "Bla Bla Bla " _ VbMsgBoxStyle.vbYesNo If Response = vbYes Then Me.Stato.Value = "A Scadere" Me.Assegnato_a.Value = "" Else MyString = "No" End If End Sub
I am finding it more and more difficult to answer questions lately, is it just me or has the quality of the questions gone down?
In some cases it appears that the "question" is more of a request to have something done for them than actually trying to solve a problem that they are having with a learning process.
creating a small loop to run code.For each record from AREA with PLANTPROGRAMID = VARIABLE Then...In this example, my table is called Area, my field is called plantprogramid, and my variable is defined earlier in code.
I am having trouble with opening an existing form in ms access. Any idea what could be the problem? Even if I am not able to retieve the whole form is there a way I can retrieve all the event procedures associated with the form?
In the Access application that I have developed for a user, the application is supposed to establish the ODBC connection to an external database when the application is initiated. On my PC, the application works. On an older PC, the application works. However, on the User's PC and on the new one that we are building for her the linking to the external tables does not work. I have displays for the tables that she is to link to as the program is doing the linking. Sometimes, on the PC for her use, the first table will not link and the rest will. Sometimes, none of the tables will link. Sometimes, the first half will link and the second half won't. I am stymied. Below is the logic that I use to make the connections at the beginning of the process. Any suggestions?
Dim strTable As String
On Error GoTo Assign_Error
'MsgBox ("Click on OK and Please be Patient as the program links to PeopleSoft tables.")
' NOTE : Do not unlink the tables as you will have to manually relink the tables
End Function Function link_table(strTable As String) Dim dbs As Database Dim tdfLinked As TableDef Dim strDatasource As String Dim strDatabase As String
Set dbs = CurrentDb Set tdfLinked = dbs.CreateTableDef(strTable)
' tdfLinked.Attributes = dbAttachSavePWD ' Check to see if table already exists and if it does then do nothing ' For Each tdfLinked In dbs.TableDefs ' If tdfLinked. = strTable Then tdfLinked.Connect = "ODBC;UID=sysadm;PWD=sysadm;DSN=" & strDatasource & ";" tdfLinked.SourceTableName = strTable tdfLinked.Attributes = dbAttachSavePWD
dbs.TableDefs.Append tdfLinked DoCmd.Close acForm, "Message" ' Exit Function ' End If ' Next ' [Forms]![Message].SetFocus ' [Forms]![Message]![Message] = strTable & " Not Found for Linking" ' [Forms]![Message].Repaint
Link_Error: ' if the error is simply that the item to delete isn't actually ' there to delete, we want to skip it. If Err = 3265 Then Resume Next End If ' otherwise we want to show what the error is, and then exit. MsgBox Error$ Resume Link_Exit
End Function
Function unlink_table(strTable As String) Dim dbs As Database
On Error GoTo Unlink_table_Error
Set dbs = CurrentDb dbs.TableDefs.Delete strTable
Unlink_table_Exit: Exit Function
Unlink_table_Error: ' if the error is simply that the item to delete isn't actually ' there to delete, we want to skip it. If Err = 3265 Then Resume Next End If ' otherwise we want to show what the error is, and then exit. MsgBox Error$ Resume Unlink_table_Exit
Hello everybody, Could anyone out there please help me with a small coding problem that I am experiencing....
I have a calculated control on my startup form called txtCurrentAge on which I have put VB code in the on-timer event of the form which makes the control 'flash on & off' if the persons age is below 18. code as follows:
Private Sub Form_Timer() If Me.txtCurrentAge.Value < 18 Then Me.txtCurrentAge.Visible = Not Me.txtCurrentAge.Visible Else Me.txtCurrentAge.Visible = True End If
End Sub
Everything works fine but when a user inadverently clicked on the control by mistake it caused the following error to be displayed: Runtime error 2165 "you cant hide the control that has the focus". What does this mean and how best could I prevent this from happening again as I cant guarantee that any of my users wont click on it again?? The text box concerned has been made 'visible', has not been 'locked' and is 'enabled'. ' I find this 'flashing action' to be very useful and would like to keep it on my form....
I have searched on this site and various others but have been unsuccesful....
Any help or guidance would be very much appreciated. P.S I obtained this code via databasedev.co.uk and adapted it to suit my particular DB.
I have recently upgraded from Office XP to Office 2007. I had an Access database which worked fine in Access XP. In it I had a table linked to an Excel spreadsheet. When I entered data on an access form, it updated the Excel spreadsheet [source document] accordingly. This does not seem to work in Access 2007. The linked table option now does not permit data entry or amendement. The other "Get external data" options create a table in Access which also does not update my source Excel document. The idea is to enter data in Access using a form. This data is placed in an excel spreadsheet. Excel gets some lookup values, and then enters these in a Word mail merge. Without the functionality of a linked table and data entry, I cannot produce new reports.
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*") UNION SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
I am creating a database for creating quotations. The quotation number is generated using the date, for example the first quote today would be quote number "05202015-1" because it is the first one today. The next quote today would be quote number "05202015-2" and so on. Is there a way to make access automatically generate these quote numbers based on the date?
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load() Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close If InputBox("What is the password?", "Password") = "2" Then Else MsgBox "Invalid Password", vbCritical, "Sorry Charlie" DoCmd.Close End If End If
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
Hi, I've been trying to create an MDB file of my database with litle luck.
when I try and click the make MDE, the computer locks up and the processor goes into meltdown! I have to quit Access through TaskManager to bring it round.
I previously exported every element of the database one at a time to a new DB, and on each tested to see if the MDE would work. I identified 4 forms and 1 report which would prevent it making the MDE. I started the new database from there. Now I have re- created the forms and I get the same problem as before,
Does anybody have a clue what is going on or where i should look to resolve this , as I dont want to have to do all that exporting/testing again..????
Need a little help here. I have this code on a command button that open a form that displays a chart. The chart work great as long as there is data to display. However, if there is no data then the chart is blank. So what I'm trying to do is add a dcount to catch the 0 and give an error. So the 1st query makes the table where the data for the chart come from. The Qry-Test for Zero query, queries that new table if dcount is 0 it should error. My problem is that I can't get this to work with the 0 if I make it 1 then I will get the message box if there are no records and if there is 1 record 2 and greater work fine. So my question is why won't the code catch the 0?
Dim stDocName As String stDocName = "Qry-Makes Table" DoCmd.OpenQuery stDocName, acNormal ', acEdit
If DCount(" * ", "Qry-Test for Zero") = 0 Then MsgBox " There is no data for this time frame to chart. Please re-enter your Date range" Exit Sub Else
Need a little help here. I have this code on a command button that open a form that displays a chart. The chart work great as long as there is data to display. However, if there is no data then the chart is blank. So what I'm trying to do is add a dcount to catch the 0 and give an error. So the 1st query makes the table where the data for the chart come from. The Qry-Test for Zero query, queries that new table if dcount is 0 it should error. My problem is that I can't get this to work with the 0 if I make it 1 then I will get the message box if there are no records and if there is 1 record 2 and greater work fine. So my question is why won't the code catch the 0?
Dim stDocName As String stDocName = "Qry-Makes Table" DoCmd.OpenQuery stDocName, acNormal ', acEdit
If DCount(" * ", "Qry-Test for Zero") = 0 Then MsgBox " There is no data for this time frame to chart. Please re-enter your Date range" Exit Sub Else
I use the code below on a search form. I would like for the results of the list box search to populate a report instead of the list box. Is it possible to take the the sql and move it to a report? Thanks..
Dim strsql As String, strOrder As String, strWhere As String Dim dbNm As Database Dim qryDef As QueryDef Set dbNm = CurrentDb()
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form If Not IsNull(Me.txtIssue) Then '<--If the textbox txtCenterName contains no data THEN do nothing strWhere = strWhere & " (SiteIssues_tbl.Issue) Like '*" & Me.txtIssue & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef End If
If Not IsNull(Me.txtAdminDate) Then strWhere = strWhere & " (SiteIssues_tbl.AdminDate) Like '*" & Me.txtAdminDate & "*' AND" End If
If Not IsNull(Me.txtSite) Then strWhere = strWhere & " (SiteIssues_tbl.SITE_ID) Like '*" & Me.txtSite & "*' AND" End If
With Me.lstSearchResult If .ListCount > 0 Then GetListCount = .ListCount - 1 'Headings count Else GetListCount = 0 'Headings don't count if no items listed!! End If Me.Text31 = .ListCount - 1
Dim ctl As Control End With If Me.Text31 = -1 Then Me.Text31.Value = 0 Me.lstSearchResult.RowSource = "" DoCmd.RepaintObject acForm, "SitesIssues_qry" Me.Requery
MsgBox "No Records Found."
For Each ctl In Me.Controls If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Then ctl.Value = Null DoCmd.RepaintObject acForm, "SitesIssues_qry" Me.lstSearchResult.RowSource = "" Me.Requery End If Next ctl End If
I have code bellow which is bringing a tab called Heffalump from the excel spreadsheet into table. Now, what I need is to create a text box where I will be typing PI1228131313 and it's going to be changed in the code bellow.
I have a code bellow which takes data from Excel spreadsheet and puts it into table in access. I want to see this code in txtbox and be able to change the path's.. Please advice. I tried typing.. Me.mytextbox.value = but for some reason doesn't work.. Should I use Ufter Update or something like that..?
I 've converted the code to ADO method. It was working fine in DAO but know I get the following error and i can't figure it out the soloution.
Run-time error ‘3265’
Item cannot be found in the collection corresponding to the requested name or ordinal
Function ChangeQuantUnit(Material, quant_a, unit_a, unit_b)
Static M As New ADODB.Recordset Static Mat_SQL As String
ChangeQuantUnit = 0
Mat_SQL = "SELECT SAP_Materials.Material, SAP_Materials.K, SAP_Materials.Base_K, SAP_Materials.KG, SAP_Materials.Base_KG, SAP_Materials.MTR, SAP_Materials.Base_MTR, SAP_Materials.ST, SAP_Materials.Base_ST FROM SAP_Materials WHERE (((SAP_Materials.Material)=""" + Material + """));" M.Open (Mat_SQL), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If unit_a = "LB" Then ChangeQuantUnit = (quant_a / 2.2) Else If M(unit_a) * M("BASE_" + unit_b) > 0 Then 'this is the line that crashes ChangeQuantUnit = quant_a * M(unit_b) * M("BASE_" + unit_a) / (M(unit_a) * M("BASE_" + unit_b)) End If End If
What I am trying to do is delete a table and replace it with a table of the same name in another db. the path to the file is stored in Text1. The delete part works OK then the error message arrives, when I run it with a static file path it worked ok, but I want to pick up the path stored in Text1 on my form.:confused:
In the database I am creating a table that houses “subjects’ names” and their “unique IDs”, which are both indexed with no duplicates. The Unique ID is used as a FK to connect all the tables that contain information from the subject. When subjects are enrolled in one of our studies they are assigned a “Unique ID”, which is assigned sequentially. The IDs are alphanumeric, and consists of 4 characters with the first character being a letter:
A001, A002…A999, B001, B002…C001 etc.:
To help avoid data entry errors, I would like to automate the process of assigning these Unique IDs as much as possible. So my question is:
Is it better to manually insert all the possible Unique IDs into this table upfront and then use a code that allows the user to assign the next Unique ID to the subjects they are enrolling (ie search the subject names column for the first null field and enter value there)? Or would it be better to set up a code that looks through the IDs that are already in the list and then automatically generates and assigns the next sequential ID to the person they have entered.
Also, where might I find some pre-established code to get me heading in the right direction?
