Hi guys,
Still trying to get used to this Access lark.
A table contains a column with numbers i.e - 1,1,3,4,2,2,2,3,4,4
If i just want a query that shows one of each i.e - 1,2,3,4
what do i need in the criteria line?
Thanks in advance
Hi,
I have a form(frmentry) where a user creates a record and it has a primary key that is entered manually the primary field is called "AR_Number". The user enters the details in this form and then click a button called "butapprove" and it launches another form called 'frmentryapproval'. When this second form loads up i need it to be on the record that the user was just entering, wondering what the best way is to load that record into the form.
currently I am using this code:
Private Sub butapproval_Click()
Dim appformname As String
Dim apparnumber As String
appformname = "frmentryapproval"
apparnumber = "[AR_Number]=" & Me![ARNo]
DoCmd.OpenForm appformname, , , apparnumber
End Sub
but when using that it does not work properly and the values entered on the 2nd form are not saved at all, let alone to the required record.
I have seen some things on GoToRecord & FindRecord but don't know how to use them, would the be useful in this situation?
Thanks for your help and sorry for the n00b question.
Ryan
Hi,
I have around 50 jobs on a table. These have a 'start date' of various dates in a year. A field called 'complete' indicates whether a job has been closed off or not and a field called 'frequency' determines how often the jobs recurr (in days).
I need a query (which I'll run every night) which will examine all the incomplete jobs on the table and copy all this data to new jobs and change the 'start date' to the previous 'start date' + 'frequency'.
That in itself is not the problem. My problem is how to tell Access that when the current and next instances of a job exist, not to copy it a third time.
In other words, after the query runs there should always be 2 instances of the job on the table...current and next.
Can someone please advise me what I should do?!
Thanks in advance
I'm sure this is simple, but I can't find it anywhere...
How do I refer to an instance of a form using its key value?
For example, I have a collection 'colForms' and two forms in that collection with the keys 'Key1' and 'Key2'. How do I refer to the instance that has Key1 in code (for exampe, to close or requery the Key1 instance of the form from another form)?
Thanks in advance,
David
dminstance
I am trying to ONLY allow One instance of a value in my table.
tblUserSecurity
admn
dev
If either of these exist (They are Yes/No) then do not allow. This is being checked via a combo box on my form using the after update event. Only One Administrator and One Developer. DCount or DLookup ?
Hi all,
I have some records containing the following data
ABC (UK)
ABC (UK) Ltd
ABC UK) Ltd
ABC UK)
I need to pull out all the data with a ) but not a ( and add a ( to the start of the word before the ).
So for example, the four examples above would change to:
ABC (UK)
ABC (UK) Ltd
ABC (UK) Ltd
ABC (UK)
I have selected the companies that need updating, and I can pull the data to the left of the ), but I don't know how to find the first instance from right to left of " ", or find the last instance from left to right of " ".
Can anyone help?
Thanks.
I'm making a database that keeps track of particular lots of ingredients going into certain recipes.
The relevant tables are
tblSupplements (SupplementID, SupplementName)
tblSupplementLots (LotID, SupplementID, ArrivalDate)
tblRecipes (RecipeID, RecipeName)
tblRecipeItems(RecipeID, SupplementID, Quantity)
tblSupplementRecipeBatches (BatchID, RecipeID, MixingDate)
tblSupplementRecipeBatchItems(BatchID,LotID)
I have the top 4 tables filled out via forms and it all works swimmingly.
I'm having a hard time coming up with a scheme for filling out the bottom two.
I would like to have a form which will display the BatchID (automatically generated), allows the user to enter the MixingDate, gives a combobox bound to RecipeID which is filled from a pulldown menu based on RecipeID and RecipeName from tblRecipes.
All these I can do, but the next step is befuddling me: based on the RecipeID, I need to make a list consisting of the varying number of supplements which comprise the given recipe and allow the user to enter the LotID for each one (preferably from a pulldown menu).
I've tried a bunch of things (subforms linked on RecipeID, listboxes, VBA update queries attached to buttons). I just can't seem to understand how to have a variable number of subrecords appear and attach a new field (LotID) to each. Perhaps my table design is awkward?
I need to open multi instance form.
1) i have a table (SQL) "ActiveForm" which contains various fields including a field called "FormName" (Varchar (30)).
2) in the access form called "Menu" i place a treeview control and when i "click" in the node, i read the table rsProcedure="SELECT .* FROM ActiveForm WHERE Active=true";
3) Dim Istanzaform As Integer
Dim dbs As Object
Dim myFrm As AccessObject
rsProcedure.MoveFirst
rsProcedure.Find "DescrizioneForm = '" & Node.Text & "'", , adSearchForward
[Code] ....
Hi,
I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:
Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True
The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.
I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.
Any suggestions would be much appreciated.
Thanks,
Keith.
I have two problems excel instance does not close at the end and two when I try to do the looking using text it works but soon as I try using date/time it returns nothing.
Code:
Private Sub Command84_Click()
Dim objExcel As Excel.Application
On Error Resume Next
[Code].....
I use the following code to get the first and second instances of a "/" character. How to get the position of the third instance.
iUPC = "123-7754LF-(A/S red Top)-T19/97876564"
'get number of instances
xTimes = 0
xTimes = Len(iUPC) - Len(Replace(iUPC, "/", ""))
'get position of characters
xInstance1 = InStr(1, iUPC, "/")
xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")
I just want to run a series of DoCmd.TransferSpreadsheet commands to export several queries to one Excel Workbook with seperate sheet names for each query.
However my problem is I want this to be a new instance of Excel that the user will then SaveAs after it is complete. I don't want to save it to a specific path first because the Db is on a shared drive and My Documents will have a different path for each user based on their user name. (corporate environment)
There must be some way to just have it open a new workbook without saving isn't there??
I've been trying to determine what recordsource to apply to a listbox to get the following results for its three columns:
Column1 Column2 Column3
AddressID Address Owner
The data is being pulled from two tables:
tblAddress
AddressID (pk)
HouseNum
Street
Apt
tblOwner
OwnerID (pk)
AddressID (fk)
LastName
FirstName
Current
Problem: I need each record from the Address Table to be shown with the most current Owner information from the related table. The most current Owner Information record is identified by a checkmark in the 'Current' field.
I've tried the following queries:
This query gives me duplicate addresses for each instance of owners:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID;
This query gives me only addresses that have a current owner, the problem is, sometimes a house won't have any related owner record in the owner table:
SELECT DISTINCTROW [AddressID], [HouseNum] & ' ' & [Street] & ' ' & [Apt] AS Address, tblOwner.LastName AS Owner FROM tblAddress LEFT JOIN tblOwner ON tblOwner.AddressID=tblAddress.AddressID WHERE tblOwner.Current = True;
Can this even be done with a query? This is driving me nuts. Help!
Scott
my code does the following, user selects excel file, opens it, renames sheets, basically needs first sheet to be sheet1. rest don't matter Changes the formats in column a to number and 15dp, saves the file as .xls and then links the file to the database.
Code:
Private Sub Command288_Click()
Dim s As String
Dim i As Long
[Code]....
what the difference between the two variations of using the New keyword to create a new instance and using the Set. I mean Code 1 would always be better because you dont have to ever use the Set keyword
1.Code:
Dim wb As New Excel.Workbook
2.Code:
Dim wb As Excel.Workbook
Set wb = New Excel.Workbook
Are there benefits and cons to both i.e. memory allocation etc..
How to set focus to a non Default instance of a form.
Environment
A2007 ADP Project
Document Window Option - Tabbed Documents
MS SQL 2012 Express DB
Windows 7 64 Bit
I using Allen Browne's method to open more that one version of form, storing each form object in a collection declared in a module. No problem there.
Now I'm trying to add a command button on an form to set focus to one of these non-default instances already open.
The form I am trying to set focus to has a the following related properties
Default View: Split Form
Pop-up: No
Modal: No
The form that has the command button on it is of the same type.
Here is the code I've tried:
'Code on Calling Form
Private Sub cmdProjectList_Click()
Dim FunctionResult As Boolean
If AppForms.GoToForm("ProjectList") = False Then
AppForms.Load_ProjectList
[Code] .....
The code compiles and executes with seemingly no problems. It finds the form loaded, then cycles though and finds the form in Forms but the SetFocus call seems to do nothing. When I run the code against a defualt instance ( one not opened using Allen Browne's method) it works fine and sets focus to it as expected.
I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.
Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]
[Code] ....
is it possible to prevent
1- running multiple instance of MSACCESS.exe in runtime
2- running multiple instance of same database in runtime.
i have found seems not to work on access 2010 runtime.
I am using the method from allen browne [URL] .... to open a form and add it to a collection and when removing it it closes. actually, usually it does work so but i have now a form which does not close until i am hitting the reset button in VBE , is there something i could check why it's not functioning as desired ?
Just to add, this form has a subForm as well (might be the cause ?)
Hello All,
I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!
From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.
I’d be very grateful for any advice/guidance.
Bomac.
I have a table with customer details, probs over 3000 customer, and i need a method of selecting the customer in a form by their surname. Currently i have a combo box, but as you can imagine, this is not practical with so many records. anyone think of any other way to do it?
View 4 Replies View RelatedHi,
I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales).
It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
Any ideas?
Slighlty confused,
Rosxx
Can anyone tell me why sometimes when i'm using access all of a sudden when i click in cells, control boxes etc that i select everything and i cant position the mouse with a string of text with selecting all of it.
VERY frustrating.
cheers,
Spinkung :confused:
hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!
My problem is as follows:
I have a form for my products and a subform for the suppliers, each product can have many suppliers.
I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(
can anyone tell me how to select only the supplier with the lowest supplier price for each product
Cheers guys
Good Afternoon,
I am currently trying to create a query that will allow me to do the following:
Prompt the user with a pop up box, asking:
Please enter start date
Please enter end date
It will then select everything from my table between those two dates (this works), and then it will ignore all those items that have a time after 7am or before 7pm (this works), but I need it to also select all entries that fall on a saturday or sunday. Now because my raw data comes in the form of (australian dates): 7/09/2005 9:39:10 AM, I am using an update query to split the field into two fields: DateFrom and TimeFrom (both are date/time fields). I then need to create another field called Day, which takes the data in DateFrom and converts the date to dddd (using Format(Date()) i assume), giving me a field with the day name in it, ie: Sunday.
I can then use that to differentiate between weekdays and weekends.... as I use MySQL and PHP more often than I do access, I have no idea how to do this.
So in short:
How do I (this is what i think i need):
Select * from tblData WHERE Day!=Sunday OR Saturday AND TimeFrom > 7:00pm OR TimeFrom < 7:00am
Select * from tblData WHERE Day=Sunday OR Saturday
Both of those selections, will of course need to be performed within my date constraits specified by the user.
Am I making sense?
Hallo Everyone,
I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.
aravind.s