Is there anyway to determine the drive letter of a flashdrive inserted into a PC ? I have a backup routine that backs up a MS Access database to a flashdrive. I need to determine the flashdrive letter.
View RepliesI need a module but haven't got the foggiest on where to start. I have a query that works out whether a staff member has done a sleep in based on their clocking out time (TIME OUT). I also have a column for DAY IN which it stores the day they logged in and HOUSE which indicates which house they clocked in at. What I want to do is determine whether each 'sleep in' is a weekday or weekend shift and at which house. We have different rates of pay based on the houses and whether it is weekend or not.
View 5 Replies View RelatedHi,
I'm designing a database in which the primary key is a combination of the first letter of first name, the first letter of last name and the date of birth.
How can I do that?
Regards,
CS
I have a table that has a field for month represented by a number. I would like to report by month but group by year. The report would group months 1-12 into year 1, months 13-24 into year 2, months 25-36 into year3, etc..... up to possible 20 years. Is there an easy way to identify and group the year? I was thinking of adding another column to the table and when I write the values to it include a year. I would rather do it some other way as the data is spilt and would require a lot of table modifications to distributed db. It would be easier to do it with a query or some code in the front end.
View 2 Replies View RelatedHow to search for a file using the dir function, however, i need to search for a file from a main folder which may have several sub folders.
I am having no luck so far - i understand there is a recursive search facility but im not sure how i can get this to work with what i am trying to achieve.
This is the code i have for the dir function:
If Len(Dir("as-tamworth-50midlands.qa$TamworthLaminate C of CsCircuit Foil" & CofC & ".")) = 0 Then
MsgBox "This file does not exist"
Else
MsgBox "Yippee"
End If
I am having trouble with a If statement. I am trying to save a record into a table when an item is Selected (highlighted blue) in a list box. Keep in mind that my listbox is set to multiple selections. I have the following code but sometimes it works and sometimes it doest.
The reason why I am trying to get this to work is because when a item is selected I will add it to the table and when an item is deselected I will delete it from a table. See my code and pictures I have attached :
Private Sub list_audits_Click()
Dim strsql As String
Dim list As String
Dim id As String
id = Me.User_ID.Value
[Code] ....
How to determine if a field within a table is a primary key or part of a compound key using ADO?
View 1 Replies View RelatedI have a form designed to update content on a website. I'm trying to work out how to populate fields with entries from past dates.
I have set up two combo boxes for each 'spot' on the site, one for date: Feature_1_Date and one for which content spot the content was occupying Feature_1_Spot.
The idea being that these boxes would read the row from my table to find the date, and the column to find which spot the content did occupy to copy the same content into a field Feature_1_Content
From what I can gather I need to use DLOOKUP (I'm very familiar with VLOOKUP on excel) but I'm doing something very wrong as can't get it to show any content.
I'm creating a folder with values from some fields. It all goes OK, but, the only thing is that the name of the folder could get to long.
We use words like "Prototype Model" "Prototype Review" Next Date Review" "Next Prototype Review"
How could I end up with only the first letter from each word like "PM" "PR" "NDR" "NPR" from a field called Type_Review ?
I have an MS Access database that sits in the users local area e.g. C:Users....
I have the following code which will open a user guide on a server shared by all users.
Code:
strFilename = GetUNCPath("T:") & "19_InformaticsInternal Offer Transfers" & "Utility User Guide.doc"
Users have different drive letters for the server name. In my case it's "T:" but for others it may be"S:".
Is there any code which would determine what drive letter each user has? The server name is
denasvf1acorpHR_GLOBAL_1HR_Shared_Service_Data
This for an app being used in Access 2003. I'm trying to (sort of) automate naming a PDF report. After the user selects a printer from the print dialog, if the printer name is like "*PDF*" then I want to put the PDF file name in the clipboard so it can be quickly pasted in the file name of the PDF printer's save dialog.
Something like this:
Code:
Dim strSelectedPrinter as string
DoCmd.OpenReport "rpt1", acViewPreview
DoCmd.RunCommand acCmdPrint
[Code]....
but that returns the name of the default printer, not the selected printer.
I'm working on a report called Open Orders and when the database loads, it takes you to a Navigation Form. You make some selections mostly from combo boxes, then click run report which runs a query then launches the report.
I want the user to be able to click a check box called "Ready Only". If the checkbox = True, then I would like the field "Ready Pieces" in the query to have the criteria ">0". If the checkbox = false, I want that field to show all values (*).
I have no problem setting the criteria of a query field to equal that of a combo box value (Warehouse Like ([Forms]![Process Form]![Warehouse] & "*") but have problems when the criteria isn't the exact same as the value of the control.
Things I have tried to no avail: Putting a Iif statement in the query criteria: gives an error that criteria is too complex Creating an invisible text box whose value is determined by the checkbox to ">0" or "" then basing the Ready Pieces criteria equal to this....doesn't work Trying to use the DoCmd.RunSQL with my SQL code that changes via VBA when the checkbox is changed...Get an error and the SQL doesn't run
I am writing code to Refresh table links. I only want to refresh the Access table links and ignore the Excel Table links. Is there a way to determine in VBA if the table link is pointing to an Excel file?
View 8 Replies View RelatedI have a database with two tables. One with a schedule that lists all of the events with the person assigned to, with there start date and end date in separate columns.
My second table lists a persons leave. So I have there name and the start and end date of their leave in separate columns.
Is there a form/query I can create using VBA that will allow me to filter out all the criteria that has the same name and then a date that conflicts between the event date and the leave date.
for example,
If Name(in table 1 ) = Name (in table 2)
(if false stop)
AND,
If StartDate (from table 1) and EndDate (from table1) conflicts with StartDate (from table 2)and EndDate (from Table 2)
(if false stop)
If true I want either the query to register it OR have a form appear to show the conflicting entries.
I am using Excel as a frontend and Access as a backend. Now in the main form the user types in all the information about a customer and press Save. Suppose he stores 10 customers details so I am using PrintSpool Number . For those 10 records the printspool number will be save so that when the user press Print button then all those 10 Word document letters will get printed off. This works absolutely fine. Now I want to do the following checks in the code while printing:
It will make it easier on the watermark issue as it changes the below
First check
IF AXAFRIENDS = FLC use NO WATERMARK and END conditional (do not do second or third check)
IF FALSE move to second check
Second check
IF Team = LTC use LTC and END conditional (do not do third check)
IF Team = WINTERTHUR use WLUKCAP4 and END conditional (do not do third check)
IF FALSE move to third check
Third check
IF AXAFRIENDS = FRIENDS use PAP107 and END conditionals
IF AXAFRIENDS = DM use PAPSLD and END conditionals
AXAFRIENDS is the name of the field and the above conditions will result in different watermarks for each customer letter. My code doesn't work as required. Suppose 10 records in Access table have same PrintSpool number (Textbox1) ,then it should check for each record the AXAFRIENDS field value and display appropriate watermark during PRINT.
Code:
Private Sub CommandButton3_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Set cn = New ADODB.Connection
[Code] .....
Had a combobox so the user could choose between two data entry formats.They don't want the combobox, they want the user to enter data into one or the other textbox - and that choice to trigger the format.Two groups of text box - group 1 has a single text box - group 2 has three text box. When the user selects a text box and types the first character into it. This triggers locking out the other group choice and enables the <Validate and Edit> command button.
If the user backs out (deletes) the text in a text box.It basically makes both groups available again and it disables the <Validate and Edit> command button.Basically, if the text box Change event counts a character, it changes the text box Tag to "Bust". Then it calls a common routine that checks both text boxes.The choice won't take place if the textbox gets focus. It changes if a text box has 1 or more character typed in.
Code:
Sub WhosOnFirst()
' two groups of Required field - if one textbox in a group has a character entered first
' that group becomes the Format - enables the <Validate and Accept> button that will take the next setp
[code]...
How can we store the first and last letter of the username using vba.
E.g. my name is "Daniel Rawlings" then it should display "DR".
I basically have a form which the record source is either from a table or query and all I would like to do is to have a button which when pressed will open up a previously created word template and insert all relevant fields into the template. First Name, Last Name, Address etc.
View 1 Replies View RelatedOn a form I have a textbox with a data type of Date/Time. If the user has the cursor in that text box and types the letter "t", I want to automatically insert today's date. I also want them to have the option of manually typing in a date, i.e. 05/12/2001 or use the Date Picker "calendar" item. This is using Access 2010.
View 3 Replies View RelatedI am working with the Access Treeview feature and I'd like to align the different elements in my nodes exactly below each other.
Therefore I need exact distances (if string is longer then XY, cut short, if shorter, extend to XY).
Of course I cannot work with len() and fill with spaces because letters have different widths.
Is there a way to determine the exact width of a string? e.g. in pixels?
Using Access 2007, can I return a value for CurrentProject.FileFormat WITHOUT opening the database?
I'm looking for a way to determine the file version of a given Access database without actually "opening" the database in Access (I don't want it to ever be visible at all or in any way).
So to open a database in Access 2007 "without opening it," I'm using:
Code:
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:Path-ToFile.accdb")
From here, there are at least two different "version" indicators that can be used, as in this example:
Code:
Debug.Print db.Properties("Version").Value
Debug.Print db.Properties("AccessVersion").Value
Now, if you run this code on an Access 2002/2003 .MDB file, you will get:
Code:
4.0
09.50
If you run this code on an Access 2007 .ACCDB file, you get:
Code:
12.0
09.50
Note two important factors:The db.Properties("Version") seems to be returning the JET version from what I can see in research. This is NOT what I'm after.
The db.Properties("AccessVersion") returns THE SAME VALUE for an '07 ACCDB as it does for an '02-'03 MDB, since Microsoft never updated it, apparently.
Now.... the one way that I have found that ACCURATELY describes the version of a given access database file is to use:
Code:
Debug.Print CurrentProject.FileFormat
...as this will always return, for example, 2.0 for an Access 2.0 .MDB file; 10 for an Access 2002/03 .MDB file; and 12 for and Access 2007 .ACCDB file (and there are others; these are AcFileFormat bitmask constants as described in Access support).
BUT, this CurrentProject object doesn't seem to be available (so far as I can see) when using the "open without opening" method described above.
SO, is there any way to return the FileFormat value without opening the database visibly? (Merely basing it in the extension isn't right either.... that would be easy but not accurate as several formats use MDB.
ok here is what i want.. imagine a search box, just an empty text box that allows the user to type things into it...
there will be a simple table from the database behind it, here is the typical structure of a record
<Country>Poland
<Price1>60
<Price2>85
<Price3>95
in this table there will be say a 1000 of these records...
my user is going to have to quickly search through these records while on the phone to a customer, and quote one of the relevant prices associated with that country
what i would like is this most convenient system..
as the user types in the letter 'P' just below all the records with countries that begin with 'P' are displayed (with a scroll down arrow if needed)..as well as the 3 relevant prices with that record
if they then type an 'O' into the box (which will now hold 'Po') all the records with countries beginin with 'Po' will be displayed (eg Poland)
if they delete the 'o'. once again the displayed records below the box will return to just the countries begginin with 'p'
all that is needed is for the records to be displayed, thats it.. but the adding and deleting of the letters within the text search box will need to instantly manipulate this list..
i have posted on other forums, and have been told this can be acheived in access, is it hard?
I'm trying to add a search function the searches with ever letter I add to the string in the search box. if the string is not in the recordset then vbred the textbox.
Here's my code:
Private Sub txtGroupNr_KeyPress(KeyAscii As Integer)
Set RstRecSet = Nothing
Set db = CurrentDb
On Error Resume Next
If IsNull(txtGroupNr) Or txtGroupNr = "" Then
' MsgBox "Please enter a Group Number to use as the search criteria", _
[Code] ....
I am trying to use a DLookup to search to find what block goes into the production of an item.
In a table called "dbo_vw_MCE_job_with_materials", each item contains a list of the parts which go into its production in a column titled "Expr1". However there are always more than one parts to this, with varying names.
What I am trying to do is to use a Dlookup to only return the first value which starts with B (as the block number is always the only part on the list which begins with B).
At the moment my DLookup would look ike this:
Code:
MixTypeTxt = DLookup("Expr1", "dbo_vw_MCE_job_with_materials", "[item] ='" & Forms![Theta Input]![ItemNumbertxt] & "'")
However I think as the item column contains the same item number in 8 columns, with different values in the Expr1 column, Dlookup only returns the top value "A-CF0057" (which is irrelevant as far as I am concered) - whereas I am hoping it will be able to pull the "B1499" value from the column as it is the only one starting with a B
Is there anyway to specify which value the dlookup function would return, or is there any way to apply some sort of permanent filter on my table?
Question: Is it possible, using VBA, to determine the actual Excel file type without opening the file?
I receive data files from other departments. Seems like every time someone changes their download structure, I end up with file types that do not match the download extension (example: xlm file with a xls extension). The files can't even be opened because of this. I think I can fix it if I could figure out how to determine what the file type really is.
I'm using Office 2010.
I am trying to determine the state that a job is located in. If the ProjectID begins with a 2 then it is in California. If it does not then the job is located in Nevada.
State: IIf(Left([tblMainFrontierUnits].[ProjectID],1)="2","CA","NV")
What is wrong with this statement? I am getting a compile error.