I have a Main form (MainForm) with two subforms (SFormA, SFormB). These SForms are connected to the MainForm by a control (controlA) and connected to each other by a control (controlB) via an unbound control in the main form, in order to change data in SFormB according to selected record in SFormA. SFormB has an on current event procedure determined to show different things depending on the value of a bound control that changes every time I select a different record in SFormA. Everything works fine when Mainform and SForms first load.
But, when I select a record on SFormA I get a runtime error and everything stops, application closes and reopens, creating a safety copy. It seems to me that the on current procedure on SFormB causes this problem, because if I delete it no error appears (SFormB not showing what I want though). Funny thing is I have another database, same design, same structure, same procedures, (different objects and names of course) and it works perfectly alright. I even recreated the whole database still the same error.
I would like to set up a picture in the form that changes based on a combobox selection, for example if you select from combo box list "design1", a picture that have a name : design1 will appear as a background to the form ..
While I have developed many databases using other RDBMSs I'm afraid I'm something of an Access 2007 newbie so the vast bulk of my experience counts for nought given Access' many "unique features".
What I have is a form with about 3 text fields, 2 date fields and a number of comboboxes on it. Although, for now I'm not worrying about the combo boxes until I get the other fields working.
I am trying to develop an update screen where the user types the primary key value for a record into the first field and using DLookup retrieve the other values from the table and insert them into the form so the user can adjust them if they wish and click the Save button to write the changes back to the table. That's the theory.
Unfortunately the reality is, although the values added to the other fields using DLookup are "there" they don't appear on the screen until the controls lose focus. My best guess is because they are being added programatically and no Return key is being pressed, focus will not change and they will never appear.
So to get around this I put a line of code in the GotFocus event of each control to move focus on to the next control, effectively "looping" the focus once around the controls. This worked well until the focus was being moved from the second last control to the last control when I got, Runtime error 2110: Can't move focus to this control. The control was visible and enabled, as was a dummy control I tacked on the end to see what happened.
Main code block in Exit event of first field:
Private Sub Text6_Exit(Cancel As Integer) Dim varItemType, varItemTypeCode, varItemClass, varItemClassCode, varDescription As Variant Dim varPurchaseDate, varWarrantyEnd As Variant Dim varItemTypeTxt As AcTextFormat Dim PurchaseDateSQL As String
I was trying from a button on my form that will go to and bring up the desktop. I want it so I can put this database on anyone's computer and the button will work.
I am using this and it works but only on my computer.
Application.FollowHyperlink "C:UsersBryanDesktop"
I also tried this
Application.FollowHyperlink "C:Show Desktop.scf" And Application.FollowHyperlink "C:WINDOWSSystemShow Desktop.scf"
Both gives Runtime error 490 cannot open specified file.
Run into a bit of an issue with a combo box that has dates. When I click on a date I receive:
Run-time error '3464': 'Data type mismatch in criteria expression'
Am I receiving this because I have declared it a string when I should declare it something else?
My other question for whenever I get the first part fixed. Is there a way that instead of having all the possible dates to select from, that I could simply have three options available: Within a year, More than a year, and no date. (Once I click on one of those three options, I will filter records that have a date within the past year, over a year, or no date at all. How would I be able to achieve this?
I have a main form with several subforms in tabs. From one of the subforms I list linked records to the main form (Clients) within that subform (Bank account details). I list the records and have a button to add new records.
Where the Client has one or more records in the subform the add button works perfectly.
When the subform has no records the add new records button produces the following error "Run-time error '2498' An expression you entered is the wrong data type for one of the arguments"
The add button has
Private Sub Command52_Click() DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId End Sub
the "AddClientBankDetailsFrm" popup form has
Private Sub Form_Load() If IsNull(Me.OpenArgs) = False Then MsgBox "Form was opened with ClientID = " & Me.OpenArgs Me!ClientId = Me.OpenArgs Else MsgBox "No ClientID was passed." End If End Sub
ClientId is the primary key of the main form and the secondary key in the new record.
I have a form that has a button that calls the following code:
Sub ClosePO_Click() Dim db As DAO.Database Dim mvalue As String, strSql as string Set db = CurrentDb mvalue = Me.Combo73 'combo box on OpenPO Form strSql = "UPDATE Print SET OpenPO = NO where [GPO Invoice Number] = '" & mvalue & "'" Debug.Print strSql db.Execute dbFailOnError db.Close Set db = Nothing End Sub
When I run (or step through the code, I get the Run-Time Error 3078 ... database cannot find the input table.However, when I copy the debug.print output in the immmediate window and paste into the query builder, the query runs. Here is an example of the debug.print output:
UPDATE [Print] set OpenPO = NO where [GPO Invoice Number] = '40333' where OpenPO is a yes/no field and [GPO Invoice Number] is a string
My database was working fine. Now, if I have to save or edit a record, I get a message Run time error 2107. The value you entered does not meet the validation rule defined for the field or control. I am able to enter and edit new records in the table. I did not change any validation in form. Then it goes to debug mode and highlighted this code (me.recordsource=Listsql - is highlighted)
Private Sub ListSearch_Click() Dim dBS As Database Dim Rst As Recordset Dim Listsql As String Set dBS = CurrentDb() Listsql = "Select * from MasterData where employeeid ='" & [Forms]![MASTERFORM]![ListSearch] & "'"
Having set a table short text field to 'Indexed (No Duplicates)', I have a form which produces a '2105 runtime error' when the user attempts to submit a duplicate value. I would like to replace this default error message with a more user-friendly MsgBox.
My code for the SaveRecord button is:
Code:
Private Sub SaveRecord_Click() DoCmd.GoToRecord , , acNewRec MsgBox "Record successfully saved", vbOKOnly + vbInformation, "Record Saved" End Sub
My code to capture the 2105 runtime error is:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 2105 Then MsgBox ("This villa booking has already been logged.") Response = 0 End If End Sub
Unfortunately when the save button is clicked (when attempting to save a duplicate value), the 2105 error still runs. What are I doing wrong?
This is one of this only times i've had a problem whose answer i couldn't find on this or other forums, so here's the question.
My DBase links to the back end tables tblBrkdn tblBrkdnArchive tblBrkdwnTradespeople tblBrkdwnTradespeopleArchive
The following two tables are local, not linked tblBrkdnArchiveTemp tblBrkdwnTradespeopleArchiveTemp
After running through the following code, all of the four above mentioned linked tables are now local. It's vexing. I'm terribly vexed. If you can help, my status would chnge from vexed to joyous....:
Thx in advance...
' 1) Join all together (Each Table) ' Create two recordsets, One for the Brkdn set, one for the Tradespeople Set. ' Brkdn: Want it to be the Union of all records in (tblBrkdn, tblBrkdnArchive) without duplicating BrkdwnID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdnArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdn.* FROM tblBrkdn INNER JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdn.* " SQLStr = SQLStr & "FROM tblBrkdn LEFT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE (((tblBrkdnArchive.BrkdwnID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdnArchive.* " SQLStr = SQLStr & "FROM tblBrkdn RIGHT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE tblBrkdn.BrkdwnID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' BrkdnTradespeople: Want it to be the Union of all records in (tblBrkdwnTradespeople, tblBrkdwnTradespeopleArchive) without duplicating ID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdwnTradespeopleArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* FROM tblBrkdwnTradespeople INNER JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople LEFT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE (((tblBrkdwnTradespeopleArchive.ID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdwnTradespeopleArchive.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople RIGHT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE tblBrkdwnTradespeople.ID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'delete everything from the four non-temporary tables 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 2) Paste the entire record set to each Brkdn table 'tblBrkdn SQLStr = "SELECT * INTO tblBrkdn FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "SELECT * INTO tblBrkdnArchive FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 3) Delete from Active & Archive Brkdn tables WHERE [DATE] < txtArchiveDate.Text 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn WHERE ([DATE]<#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "DELETE * FROM tblBrkdnArchive WHERE ([DATE]>=#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 4) Paste to Active Tradespeople table WHERE Associated Breakdown Record [DATE] >= txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeople FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdn ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd n.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 5) Paste to Archive Tradespeople table WHERE Associated Breakdown Record [DATE] < txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeopleArchive FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdnArchive ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd nArchive.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True ' 6) Clean up: Delete all records from the temporary tables 'tblBrkdnArchiveTemp SQLStr = "DELETE * FROM tblBrkdnArchiveTemp;" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
On a form I have a combo box - let's call it cmboSickness. it has two options "flu" and "malaria". On the same form I have two command buttons - one ehich opens a malaria form and one which opens a flu form. If I select flu then I want the words on the flu command button to change to red. How do I do this and where do I put the code?
I have tried - If me.cmbosickness=flu then cmdflu.color=255
Not sure how to work in the '* ROLL *' into this SQL statement. The query statement works fine ... I have tried different quotation methods ( Not Like " & " ' * ROLL * & ' " & " ) AND .... )
sql = "SELECT DISTINCTROW Sum(CDbl([Scrap Factor])) AS SumOFScrap FROM [RT: Signpro1: Costs] LEFT JOIN [DT: InventoryExtend] ON [RT: Signpro1: Costs].[Part Number] = [DT: InventoryExtend].[Part#] GROUP BY [DT: InventoryExtend].CategoryID, [DT: InventoryExtend].Description, [forms]![signpro sign estima parameters]![combo14] HAVING ((([DT: InventoryExtend].CategoryID)=30) AND (([DT: InventoryExtend].Description) Not Like '* ROLL *') AND (([forms]![signpro sign estima parameters]![combo14])=1));"
Ive created a database using the file system object that creates folders, moves files from one folder to another, etc.
This works perfectly on my home pc, but when i try to install it on the server at work I get the following error message:-
Runtime Error 429
Active X component can't create object
I've had a look on the net and appears that it only happens on certain computers and I wondered if anyone had any quick fixes that they had come across over the years.
I have this error when on: DoCmd.GoToRecord acDataForm, "PaycheckTable", acNewRec Whent I trace with the debugger when the ENTER keyy is pressed event.
If I don't use the debugger I don't see any error. This problem occurs when I check a specific checkbox and trying to go to a new record (Whith the other checkboxes I don't have a problem).
Another symotom: When I use the next or new record buttons I don't have any problem.
Using Access 2003 and Word 2003 on a WinXp Workstation with all SPs and updates.
The code that I'm using is MS standard (see below) and has never previously given me any problems albeit using Access 2000 and Word 2000.
I've searched the MS knowledge base and googled the error but nothing I find seems to relevant to this situation.
It falls over on the '.Destination = wdSendToNewDocument' line.
Can someone help?
TIA,
Mo
--------Code----------
DoCmd.Hourglass True
If CreateWordObj() Then With gobjWord .Visible = True .Documents.Open "d:screening emplate.doc" DoEvents With gobjWord.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .Execute End With '.ActiveDocument.PrintPreview .Visible = True End With End If
I created a database for someone at work, with the main point of it being for someone to enter a date in one field, and then 13 weeks from that date, it ticks a box, and then 26 weeks from the date inputted, it ticks another box.
Now, all was working fine, but today she came to me and showed me an error that keeps appearing.
What happens is when she creates a new record on the form, if she doesn't input either a date (so the above formula can work), or the persons name in the appropriate field, a Runtime Error 94 appears, which I think says 'Invalid Use of Null', or something along them lines.
I've looked at the coding, and am a bit stuck (bare in mind I don't know a great deal about coding).
From memory, in the coding, the database looks at the date inputted, then adds 13 or 23 weeks to the date, and ticks when it gets to that point. However, if this date isn't inputted, that is when the run time error appears. I have no idea at all why this happens when the name isn't inputted.
If it's any help at all, the bit that is highlighted in yellow in VB, when I click debug, is as follows:
dStart = Me.txtStartDate
Underneath this it has the formula that adds 13 weeks to that date, and then the same but for 26 weeks.
I'm guessing I need to tell it that when there is nothing inputted in the date field, it doesn't have to do the formula. It doesn't like having nothing in the date field.
Apologies if that makes no sense.
If anyone at all can help, it would be much appreciated.
