Access :: Macro Writing

Modules & VBA :: Writing Macro To Duplicate Some Records?

Apr 14, 2014

I have a table it contains a list of opening and closing values along with a date and a couple control fields. What I would like to do is duplicate a few fields from records that meet a criteria onto the same table. After that is done there will be a few updates on the new records. Looking at it, it would be: 'duplicate' where field2 <> 0 and field5 = No and field6 = Yes. All other fields on the newly created records would be their default values.

View 7 Replies View Related

Modules & VBA :: Write A More Complex Macro That Will Start Another Macro At Preset Time

Dec 8, 2013

I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.

Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"

Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"

[code]....

View 1 Replies View Related

Writing A Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 2 Replies View Related

Help Writing An Expression

Jul 25, 2007

Hi gang, I'm an Access rookie with a problem. I have a table with three columns and I need to create a result field which will "count" the number of occurences of a value in the "CustNum" field. (There are other columns in the table as well so I can't use a "count" function) Please see my simulated results in the "Count" column. I'd like to have a query create this expression. Any help? Thanks! Sorry for the formatting, not sure how to replicate a table in my post.

CustNum FirstLast Count
9891247372KURTFENCHEL 1
9891247372KURTFENCHEL 2
9891247412GRIEGMOORE 1
9891247412GRIEGMOORE 2
9891247412GRIEGMOORE 3
9891247414JohnVerwey 1
161243818DANILOLAMBO 1
9891247198MICHAELSAUNDERS 1
9891247211CATHLEEN DELANOY 1
9891247211CATHLEEN DELANOY 2
9891247211CATHLEEN DELANOY 3
9891247211CATHLEEN DELANOY 4
9891247211CATHLEEN DELANOY 5

View 2 Replies View Related

Syntax For Writing / Can This Be Done

Apr 27, 2006

I'm not sure this can be done,but here goes it, I have 5 [plants] A, B, C, D & E. If one plant recieved [A] plant reject [Reject] then I need a response from plants B, C, D & E. If plant [B] receives a reject then I need a response from plant A, C, D and E. How would I show what other plants havent' responded>

How would I create a query for this? any idea's would be greatly appreciated

View 2 Replies View Related

Why Is It When You Are Writing Code

Mar 15, 2006

This was to dumb to write in the title. But when I am trying to write a code, the drop down, only stays down for a few seconds. Does that mean I am doing something wrong. I am trying to learn to write, but I am about sharp as marble, when it comes to writing.

Any Ideas

As usual

Many thanks

View 3 Replies View Related

Better Way Of Writing This Code?

Sep 6, 2006

Hello all

Further to an earlier post asking how to check if forms were open ...

I have a form EditCompanyForm which pops up to let people edit the details of a company. The form can be opened from one of three forms.

When the EditCompanyForm is closed I want to update the form that opened it.

So I have this rather ungainly bit of code.
__________________________________________________ ___________
Private Sub Form_Unload(Cancel As Integer)
Dim oAccessObject As AccessObject
Dim FormName As String
FormName = "CompaniesForm"
Set oAccessObject = CurrentProject.AllForms(FormName)
If oAccessObject.IsLoaded Then
Form_CompaniesForm.Requery
End If

Dim oAccessObject1 As AccessObject
Dim FormName1 As String
FormName1 = "CompaniesCallForm"
Set oAccessObject1 = CurrentProject.AllForms(FormName1)
If oAccessObject1.IsLoaded Then
Form_CompaniesCallForm.Requery
End If

Dim oAccessObject2 As AccessObject
Dim FormName2 As String
FormName2 = "CallListForm"
Set oAccessObject2 = CurrentProject.AllForms(FormName2)
If oAccessObject2.IsLoaded Then
Form_CallListForm.Requery
End If
End Sub
__________________________________________________ _

Do I need 3 separate AccessObjects?

Should I be using ... Set oAccessOjbect = nothing ... at the end?

Is there a more elegant way of doing this?

Thanks for any insights.

View 4 Replies View Related

Writing To Another Table

Nov 17, 2006

Hi,

I am fairly new to msaccess programming and am trying to build a stock control system for my business.

I am happy with everything I have done so far but there is one thing I cannot figure out.

I have set up so that the user can click a button and remove the relevant number from the stock level. The buttons are for removing 1, 5, 10 or 20 (there are reasons for these numbers). However I would also like the button to take information from the form (which is referenced to a query) and write that information, along with an autogenerated id number and the current date/time to another table. This is for our record keeping, so that we can record what is selling well etc and when the last one was sold.

How do I do this?

I have gone through the help files and my books, but I think the problem is I don't know where to start looking.

I also want to add a button that will add items to an order table. Again by taking the information from the form and writing it to another table. With this one a message will appear asking for the quantity to be entered. It will also check the order table to see if the item is already there and advise if it is.

Any help gratefully received.

Steve:confused:

View 3 Replies View Related

Writing Error

Feb 11, 2005

Hey guys,

So I'm trying to get the hang of having a Microsoft Access db on an ASP page. I'm just testing the water right now to see how to do simple stuff (selects and updates). Tomorrow I'm going to do loops. Exciting.

Anyways, the tester I have right now is doing a basic update, then select and then print the results. I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

It didn't give me this error when the field name was only one word. But because this particular field has spaces in it (and I can't do anything about that sadly), it throws this error. I tried offsetting it with the [ ] but that doesn't do it like it does for the SQL command. I searched the web and didn't really find anything of any help.

The line is:

Response.Write "NAME:" & rs("[Full Name (Name Field)]") & "<BR>"

Without the brackets it gives an exception error message.

Any thoughts?

View 3 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 1 Replies View Related

Writing A String Into A SQL Query

Mar 5, 2008

i have a string in VB (eg. "test")that will cahnge depending on the input, ihow can i search for the string and display the data into a form?

i currently have this
"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete, [Tasks].Tracking_Number FROM [Tasks] WHERE [Tasks].Staff_Name like [forms].[Main].[test] ORDER BY [Tasks].Staff_Name;"

but how can i make it so it spits the data into its corresponding feilds onto a form? (just like it would be if i performed a wizard?)

View 1 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 1 Replies View Related

Newbie Needs Help Writing Query

May 17, 2006

OK, I have two tables that have pretty much the same data in them, but, the first table has SOME data that the second table doesn't and I need to get that data into the table that does not have it.

Here's a description of what I want to do:

Table #1 has about 10,000 lines of data with the employee SSN as the ID for the records. In this table are two extra columns of data (HRContact)and(HR ContactCode) that are not always populated in Table #2.

Table #2 has about 300,000 lines of data with the SSN as the ID field. Some of the records that match the SSN's from Table #1 have the data HRContact and HRContactCode, but not all of the records have those fields populated.

So, what I need to happen is for the query to go through Table #1, find the SSN of a record. As it finds each SSN, it goes to Table #2, finds that same record with the same SSN, then looks in the HRContact field to see if there is data there, or if it is Null. If there is data in that field, then it goes on to the next SSN in Table #1 and repeats the preceeding process. If the data in HRContact is Null in Table #2, then it goes back to Table #1 and grabs the HRContact and HRContactCode data for that record and writes it into the HRContact and HRContactCode field for the record in Table #2. the query would repeat this process until it reaches the end of file in Table #1.

I hope this is clear and if you have any questions, please ask me...

Thanks for your help,
Dave

View 1 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 2 Replies View Related

Writing Out All Records From And To Date

Jun 28, 2007

Hi! I have a problem. The code seems ok to me, cant understand it! I cant write out all records between to dates. The dataarea in the table are in the format: General date

Can someone help me?

SELECT * FROM Feil_problem WHERE Feil_problem.dato >= '01.01.2005 00:00:00'
AND Feil_problem.dato <'01.01.2005 00:00:00'

View 6 Replies View Related

Help With Form Writing To Table

May 17, 2005

I hope this makes sense?
I have a main form with a button that opens a new form. On the new form I have 5 fields. 1 of those fields is the Employee ID which I have the form getting from the main form (IE the Control Source is =Forms!Search_Employee!Employee_ID )and the other fields are bound to my table for the new form. I am unable to get the Employee ID to show in my table, it is not updating or writing the value to that field. Not sure if there is code or how I should or can get this field to write to my table. Any Ideas or be very appreciated. Thanks anyways guys I igured this one out. I was able to set the dafault value to point ot my main form so that my Control Source could point to my table and it worked.

View 1 Replies View Related

Writing Values To A Table

Oct 18, 2005

Hi,

I have a check box that gets ticked when 'work complete', it adds the parts/service totals and fills in the 'parts total', 'vat' and 'total' fields.

This works fine but I need it to write the values to the table as well rather than just displaying the values in the fields so reports can be produced etc.

Anyone know the easiest way to do this?

Thanks

Housey

View 5 Replies View Related

Writing To Two Tables From One Form

Dec 1, 2005

I am building forms from a large questionnaire and I need to write to 2 tables from one field in the form. How can I do this?
The reason I need to do this is because I have over 255 variables so I needed to create 2 tables. I want to write my case number to both files so I will have a link between the 2 files. Thanks.

View 3 Replies View Related

Help Writing An Expression For A Form

May 26, 2006

I am creating a database at my job. The database tracks how many hours a CONSULTANT spent with a CLIENT vs. the number of hours the CLIENT has bought. The hours from the CONSULTANT are entered into three different subforms (because there are a few "types" of hours). I want the total number of hours bought (entered/shown in the parent form) to subtract the hours the consultant worked (hours entered/shown in the sub forms). I half-way got it to work but I am making several entries in each subform and I need to calculate ALL hours. If someone could please help, I would greatly appreciate it. I have attached a copy of the database so to give you a better idea as to what I am talking about.

Thank you.

View 1 Replies View Related

Writing Code For Query

May 31, 2006

Hi

I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.

I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.

Help me in writing this code.

Thanks in Advance Very much
Deepak

View 1 Replies View Related

Help Writing Search Criteria

Dec 7, 2005

I am using the below code to open a form from a search form. This code works well because I could leave a search field blank, and the code would treat the blank search fields as a wild card search. Here is the problem; I want to be able to search a range of ages in addition to lastname and first name. I added two fields (�AgeStart�, �AgeEnd�) in the search form and added ([age]>= '" & Me.AgeStart & "*'and and [age]<= '" & Me.AgeEnd & "*'") to the end of the stLinkCriteria. This addition works well if there is an age range is entered into the search fields. If nothing is entered into the age range fields of the search form, access does not treat the empty age range fields as wild card like the other fields. I would like Access to treat the empty age range fields as a wild cards search. Is this possible, and if so, how would I go about doing this? Any help on this would be greatly appreciated.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Personnel_frm"
stLinkCriteria = "[last]like '" & Me.lastname & "*' and [first]like '" & Me.firstname & "*'"

DoCmd.OpenForm stDocName, acNormal, stLinkCriteria

View 1 Replies View Related

Forms :: WRITING Records Which Had Been Used By Others

Aug 9, 2015

Firstly, I create a form and sub-form relationship of which the sub-form linked with a combobox on main form. When user wondering in the combobox, the subform could display relavant records related to the invoice number selected in the combobox.

In the subform, records got a YES/NO field (named CHECKAMT) and when I used the following coding to update records in the subform, a message something liked saying the record had been used by another user, you may overwrite those records, copy those records to another place or undo and quit!!

Set TRANS2 = db.OpenRecordset("select * " & " from MSQUARE WHERE INVNO = '" & Me.INVONUM & "'", dbOpenDynaset)
TRANS2.MoveFirst
Do While Not TRANS2.EOF
TRANS2.Edit
TRANS2![AMOUNT] = Null
TRANS2![checkAMT] = -1
TRANS2.UPDATE
TRANS2.MoveFirst
LOOP

View 14 Replies View Related

Problems With Writing Data From Combo Box

Aug 6, 2005

OK, I've tried everything I can think of with this combo box. I've searched the internet for days, even bought some big fat Access reference books and read them cover-to-cover, but I still can't get my database to do what I want. This newbie would *really* appreciate some help...

I am trying to create a database which I can use to store client�s advertising expenses. I currently have three tables: tblListings (which stores basic information about each client, with the field ListingID as the primary key); Advertising Rates (which stores the different types of advertising products and their prices, with the field ProductNumber as the primary key); and tblAdvertisingExpenses (which stores each �order� of an advertising product, including the client�s ListingID as a foreign key, date, the type of advertising, and the total cost - the primary key is an auto-numbered field called OrderNumber).

I have a main form called frmListings, which displays the client�s information from the tblListings table, and includes a subform. I wanted the subform to show only the advertising expenses corresponding to the ListingID (ie. client) displayed on the main form, so I based the subform on a query which displays all records from tblAdvertisingExpenses with a ListingID that matches the ListingID displayed from tblListings. So far so good - the records displayed in the subform change correctly when the record in the main form changes.

The subform returns info from the following fields in the tblAdvertisingExpenses table: Date, ProductNumber, and TotalPrice. I want users to be able to select a type of advertising from a combo box on the subform, which looks up the ProductNumber and Cost from another table (tblAdvertisingRates), and then writes the ProductNumber for that type of advertising to the ProductNumber field of tblAdvertisingExpenses, and writes the corresponding Cost to the TotalPrice field of tblAdvertisingExpenses (and the corresponding controls on the subform). The reason I want to write the Cost from tblAdvertisingRates to tblAdvertisingExpenses is so that I can update rates for types of advertising in the future, without having the change cascade through records where the old price has already been paid for the advertising.

My problem is: a) how to get the combo box to write these various values to other controls on the form, and b) how to write the same values to fields on the subform�s underlying record source.

I�ve tried having the combo box look up all the fields from the underlying table (hiding all except the description of the advertising) and setting the bound column to the field with the price. However, the price is not the uniquely identifying field, and the combo box only writes to the TotalPrice control if all the advertising options in the list have a different price.

Sorry if this is terribly confusing. I can provide more specific details (and screen captures) of the tables, queries, forms, and relationships (if I haven�t provided enough specific details already), and would really appreciate any help that anyone can provide. Help?!

View 5 Replies View Related

Form Data Not Writing To New Records

Nov 30, 2005

Hello All

I am creating a production line database for use on a touch screen (no keyboard or mouse) which has to be really easy to use. At the start of the week i got to the point were i thought i would test what i had done to date and have come across a serious issue.

I thought that the data from the forms i am using to input the data to the main table tblProductionDetails would write into individual records but although new records are created at the appropriate point e.g. when the comand button labelled START on frmDetailsCorrect is clicked the data is not writing to the new record that is created. I thought it would be an easy thing to solve but 20 hours later and much mucking about with DoCmd.Goto's etc i am no further forward. I have attached what i have done in the hope it is something obvious and somebody will be able to point me in the right direction.

The way the db is set up is that frmDayStart needs to be opened the command button clicked and then each form opens once the OK comand button in the top right of each opened form is pressed.

It is still a work in progress so a bit rough and ready but i need to get this fundimental problem sorted before moving on.

Any help appreciated (i am not looking for somebody to do the work on the DB just point me in the right direction or point out what the error might be).

Regards

Adrian

View 14 Replies View Related

Writing To A Table Linked From SQL Server

Dec 8, 2006

Hi all,
wonder if anyone can assist me with this the SQL Server
security/write (?) issue. The SQL Server Admin has created a table for our Dept. to use and via SQL Server I can get access to it using the username and password she supplied to me. I can also link to this table in Access 2002 via ODBC which is the way I am using to write to SQL server tables. I have developed an Access application to allow one of our staff to be able to write data to the SQL Server table at a click of a button, however when I open the table the 'add new record' button is greyed out, I can not add any data to the table.
I explained this problem to the Admin as well as the way in which we access the table and she is certain that the problem lies in Access but i'm unsure how that can be the case.

Has anyone experienced this problem before ? Is there a fix and if so how is that implemented ?

Should I also post this in the SQL server forum ?

Thanks in advance,
Mitch....

View 3 Replies View Related