how to make this two fields in my form to say Y or N if the field is Not Null. Like if the field is not null = Y Else = N for the two fields. I have a picture to show what i'm talking about.
View RepliesI want to enable a few fields when i click on the Add button on my form.I have change the onclick to Event Procedure to be able to add the following code
Code:
Category_Desc.Enabled = True
My Category_Desc field get enable as expected but the Add button no long work
Code:
Private Sub add_Click()
Category_Desc.Enabled = True
Me.Refresh
End Sub
I am using Access 2010.Most of the time a tenant pays his exact rent. When that happens, I currently type in the payment (taken form the Rent field) and date of payment (current date) into a form. The date should not change.
I would like to place a checkbox into the form. If checked, the rent would be entered into the payment field and payment date (current date) would be entered automatically. Otherwise, I would just enter in the payment and date manually.
FIELDS
Rent
Payment
PaymentDate
on form chkPayment
Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.
I have my query connected to VBA code.
I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.
Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String
[code]....
I'm attempting to use Access to automate reporting emails and I'm trying to identify the best way to identify if the report is NULL (blank).
VBA:
(strReportName is set to the report. Employees is set to the Employee Name. Email is set to the employees email address. [Requested By] is the column in the report that is used as a filter. The code is functional but also emails reports that are blank. I'm trying to just send the ones that have data)
...
strOutputPath = CurrentProject.Path & "" & strReportName & "'" & Employees & "'" & ".pdf"
[Code] .....
I use the following 3 calls to add 3 fields to an Access db,
Code:
Call subCreateField("tblRootCanalTreatment", "lngMethodID", "dbLong", strPath, 606)
Call subCreateField("tblRootCanalTreatment", "txtReferencePoint", "dbText", strPath, 620)
Call subCreateField("tblRootCanalTreatment", "txtSpaceForPole", "dbText", strPath, 644)
The sub is indicated below,
Code:
Private Sub subCreateField(strTable As String, strField As String, strFieldType As String, strPath As String, lngVersion As Long)
Dim rstSerial As ADODB.Recordset
Set rstSerial = New ADODB.Recordset
rstSerial.Open "tblSerial", CurrentProject.connection, adOpenKeyset, adLockPessimistic
rstSerial.MoveFirst
If rstSerial!lngVersion < lngVersion Then
[code]....
Only the first field ("lngMethodID") gets created. The other two fields ("txtReferencePoint") and ("txtSpaceForPole") do not get created. If I exit the db before each sub call then all fields get added. Do I need to add some "refresh field" action or other action.
I have an Access 2010 database connected to several SharePoint lists. I'd like to be able to print a report (based on a query) and add that report as a .pdf attachment to the record I was looking at when I generated the report.
At the moment I can save the report as a .pdf to a local file store, then upload it as an attachment, but is there any way to skip that step and add it to the correct record automatically?
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
View 10 Replies View RelatedI have the following code which successfully opens the dialog box with filters however when I add a function with an Excel import, the filters do not work at all (no files are shown even though they exist in the directory that's opened within the dialog box). If I manually type in the filter (i.e. *.x) it still shows the files.The function at the bottom was provided by cheekybuddha from another form, I'd be lost on getting the Excel file imported into Access.Here's the VBA code (the Function is also included at bottom):
Code:
Private Sub Command0_Click()
On Error GoTo PROC_ERR
Dim strpathtofile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean
[code]...
I have an application in Access 2003. It uses VBA to open an Excel file. The file has one tab for charts and one for data. The program counts how many charts needs to be created, one per project. It then will place 4 charts per tab, creating as many chart tabs as necessary, keep one data tab. The program then writes the data and links the data to the corresponding chart. It will also write legends, and scale the charts. This all works fine under Access 2003/Excel 2003.The error I get is "Application-defined error or object defined error".The line that gives me the error is: Set chCopy = xlsheet.ChartObjects(idx).I am using the Multi-Chart option frmChartType = 1
Now I'm moving to the Office 2010 world. It is not working. Below is the code I run to do the magic. I guess something is happening in Excel 2010 that is different from 2003.I found when I comment out the error line, and other lines dependent on chCopy it creates the charts, but all the charts are pictures and not charts!!
Code:
Public Sub GenerateChart()
' Generate an Excel chart
Dim db As Database
Dim rst As DAO.Recordset
Dim xlapp As Object
[code]...
I would like to update the following code I have been using to send emails, from Access 2010 via Outlook 2010, to include my signature block with hard returns in between each line and a few above the signature.
Lets say the signature would be:
My Name
My Company Name
My phone number
My Reference
I tried next to: stMessage = Me![EmailMessage] &"My Name" & Chr(10) & Chr(13) & "My Company Name"
etc.
It does not work..
Here is the coding :
Code:
Private Sub SendEmail_Click()
On Error GoTo ProcErr
Dim stLinkCriteria As String
[Code]....
Here is my project: For a duty plan the employees are assigned to positions in a certain area.
For example:
Name. Position. Description. Map
Brown. 1. backdoor. 1
Miller. 3. frontdoor. 1
Norman. 4. left wing. 2
Hoover. 6. right door. 2
....
Now I want to show the positions within circles on the maps, with the names within dynamically, as the employees change every week.
The coordinates of the positions do not change, but not every position is needed in every duty plan.
So, my idea is to draw all possible shapes on all possible maps and make them visible or not depending on the assignement.
The duty plan is my table in access and looping through all records I would like to fill the needed shapes.
All maps are on slides (40 slides and about 400 positions) and should be filled accordingly.
I can open my presentation, although I only could do it by late binding ( a reference to ppt 14 obj. Lib fails with an error!! - why??) but I cannot any solution how to loop through the records and do the job.
have a query which I would like to export to excel 2010 and would like separate files saved using a unique field called [Brokerage]. The code below exports the query however does not export separate worksheets as I am missing something perhaps the OutputTo function.
Private Sub Commission_Excel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim mypath As String
[code]....
The following code browse from one form to another and shows the record details I select in the first form which is what I need, however, the problem is it only shows that record and will not allow me go to next or previous records when pressing NEXT or PREVIOUS by saying this is the First Record or this is the Last Record.
Anyway to modify it to allow me navigate to other records.
Dim txtJobNumber As Integer
Private Sub Job_Number_DblClick(Cancel As Integer)
'Store Job_Number in txtJobNumber variable and display
message to show value.
txtJobNumber = [Job_Number]
[Code] ....
I have a database that, I would like to add a button that performs a active directory lookup. I would like it to check a username with Active Directory, and auto populate a few fields.
First Name
Last Name
Manager
Department
This is my first database and I have very little exp using VBA.
I have two tables linked via Project ID as shown in the attached file. I need Module to concatenate Project sub types against Project Types.
View 3 Replies View RelatedI opened a 2007 Access db in Access 2010. This DB worked perfectly in Access 2007, but when I opened it in 2010 my recordset.recordcount no longer works.
This is what I have:
rsGetInst was previously defined
Dim intInstCount as integer
Dim rsGetInst As New ADODB.Recordset
rsGetInst.CursorLocation = adUseClient
rsGetInst.CursorType = adOpenDynamic
rsGetInst.LockType = adLockOptimistic
rsGetInst.open "Select * from tblInstruction where CustID = " & intCustomerID
intInstCount = rsGetInst.recordcount
At this point a get a "type Mismatch" error, and it is happening in all my recordsets recordcount.
Is there a command or reference that needs to be change when using Access 2010. When I compared the references the only difference is that in 2007 we reference Microsoft Access 12.0 Object Library and in 2010 is Microsoft Access 14.0 Object Library.
I have always had solid read access to the Windows registry running on Windows XP with Access 2007. I am utilizing code from here in a separate VBA Module to obtain access to the Windows registry:
"Change registry settings in VBA"
[URL]
On Windows 7 x64 / Access 2010 x86, I am seeing a 0 value in lngKeyHandle. Also, m_lngRetVal has a value of 2 after the RegOpenKey LOC.
Code:
' --------------------------------------------------------------
' Query the key path
' --------------------------------------------------------------
m_lngRetVal = RegOpenKey(lngRootKey, strRegKeyPath, lngKeyHandle)
' --------------------------------------------------------------
' If no key handle was found then there is no key. Leave here.
' --------------------------------------------------------------
If lngKeyHandle = 0 Then
regQuery_A_Key = vbNullString
m_lngRetVal = RegCloseKey(lngKeyHandle) ' always close the handle
Exit Function
End If
I have verified that I have the key successfully in the registry... installed via the .reg file that works on Windows XP / Access 2007 systems.
I have a split db with tables in the back end and my forms, reports, code etc. in the front end.
I encrypted the back end with a password. That worked fine.
I deleted and relinked my tables to the encrypted back end. That worked fine as well.
I have a function that will disable the shift key bypass. If I run that in my front end db then I can't save it as an accde because I can't get to the HOME screen. If I save it as an accde first then I can't run my 'disable shift key bypass' function because I can't get to the modules!
How can I secure a split Access 2010 database so that the user cannot execute shift bypass on the front end or make any changes to the code?
Previously in Excel / Access 97 / 2003 I made a little tool that would search for files in specified locations - I modified the code so that it would search for files based on what was entered in cells.
I believe a lot of the old code doesn't work in Access 2010 and it seems like a lot of the old file searching code I used to use no longer works.
Is there code available that searches a directory and lists results?
In Access 2010 is it possible to have a tblKits with a column on it Reactions 8. Then on a form a field Used. When thee user inputs 2 into the Used field can the Reactions on in the tblKits be updated to 6 automatically?
View 1 Replies View RelatedI am trying to export a table from Access 2010 into an existing multiple tab excel 2010 spreadsheet.I want it to overwrite the "data staging" tab each time.I have it adding the tab into the existing spreadsheet but it names it "data_staging" however if I run this a second time I get excel found unreadable content in 'data staging' Do you want to recover the contents of this workbook? if you trust the source of this workbook click yes.
Code I am using
'export to existing spreadsheet data staging
Private Sub Command5_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Phx Data Staging", "F:My DocumentsWorkSGN est est data staging.xlsx", False, "data staging"
MsgBox ("Completed")
i am running some code from vba to add a linked table and the do some lookups, then delete the linked tables.
When the linked table gets added, the navigation pane gets displayed. I have set it to do not display in the settings and it does not when it opens but when this code runs it opens and then stays open until the db is closed and reopened.
I am trying to use my db as a software and I really do not want this to show.
I have also noticed this in a database I have which has update queries and was fine in access 2000 but does what i described above when using in access 2010.
I found the below link that looks great and works.
[URL] ....
Te only thing is that I don't have much control over it and would like to build something similar as we also need to work with access 2003 (still )Any way to find how the code works (if there is some of it hidden in access)?
I have a MS Access 2010 application when the User opens form CONTACTS Form
◦User finds single record to be used CONTACTID is identifier to be used for selection
◦User clicks button to open form frm_MAIL_MERGE
◦Frm_MAIL_MERGE has a drop down combo box that reads the folder location where the application resides and displays all .DOTM files (that is all template files) and one more combo box which contains the CONTACTID.
◦User selects single .dotm file for merge
◦Frm_MAIL_MERGE has either drop down to select CONTACTID or UNBOUND data field for user to type in CONTACTID number
◦User enters CONTACTID to be used for the mail merge
◦User selects SUBMIT
◦Application merges selected .dotm file with the information in table CONTACTS for the selected CONTACTID
◦Merged document is saved on the user Desktop as xxx.docx
Is it possible to have a value in an excel 2010 cell be used to update an access 2010 db?
For example, if in a spreadsheet "test" cell A2=3 then in a db "test" a column "value" is 3. However, if the value in the spreadsheet changes so does the db.