What To Use In This Instance

Mar 21, 2005

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

View Replies


ADVERTISEMENT

Need Current And Next Instance Of A Job

Jul 6, 2005

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

View 2 Replies View Related

Selecting One Instance

Dec 16, 2005

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

View 2 Replies View Related

Hwo To Refer To A Form Instance

Feb 17, 2005

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

View 1 Replies View Related

Forms :: Allow Only One Instance Of Value In Table

Jul 31, 2014

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 ?

View 14 Replies View Related

Right To Left Search/ Find Last Instance

Feb 20, 2006

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.

View 1 Replies View Related

Forms :: Creating Instance Of A Recipe?

Apr 15, 2014

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?

View 2 Replies View Related

How To Open Multi Instance Form

Oct 21, 2013

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] ....

View 4 Replies View Related

New Form Instance - Suppress Record Retrieval?

Mar 11, 2005

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.

View 2 Replies View Related

Modules & VBA :: Excel Instance Does Not Close From Access

Oct 4, 2013

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].....

View 11 Replies View Related

Modules & VBA :: InStr (3rd Instance Of Character Required)

May 1, 2014

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, "/")

View 4 Replies View Related

Modules & VBA :: Transfer Spreadsheet To New Instance Of Excel

Dec 30, 2014

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??

View 3 Replies View Related

Show Only One Instance In Listbox With Multi-table Recordsource

Oct 9, 2005

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

View 1 Replies View Related

Modules & VBA :: Excel Instance Won't Close Form Access

Nov 10, 2013

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]....

View 14 Replies View Related

Modules & VBA :: Difference Between Two Variations - Using New Keyword To Create Instance Or Using Set

Nov 20, 2013

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..

View 8 Replies View Related

Modules & VBA :: Setting Focus To Non Default Instance Of A Form

Mar 21, 2014

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.

View 11 Replies View Related

Modules & VBA :: Update Recordset Based On Max Process Instance From Another Table

Jun 7, 2013

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] ....

View 5 Replies View Related

General :: Prevent Multiple Instance Of Access 2010 Runtime

Jun 21, 2014

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.

View 7 Replies View Related

Modules & VBA :: Removing Form Instance From Collection Does Not Close Form

Nov 17, 2014

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 ?)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved