Forms :: Updating Value In Table That Was Initially Searched For

Jun 20, 2015

I am trying to make an amend record form which gets values from a subform. Most of the values that I need to amend on the form do amend. My problem is that I need to amend a value that is also used to find the record.

The value in the Table(BookInTable) that I am trying to amend is Barcode which is a text value. I have been trying to pass the initial value into a string(Bar) and have been using an SQL Update string.

Code:

Private Sub Command23_Click()
Dim Bar As String
Bar = Forms!FrmAmendOrder.AmendOrderSubform!BarCode
If IsNull(Me![POTxt]) Or (Me![POTxt]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![POTxt].SetFocus

[Code] .....

BarTxt and POTxt are textboxes that show the initial values and BarTxt is editable.

By adding watches I can see that when I click the button Command23 Bar = the initial value and BarTxt.value = the edited value. But when I look at the table nothing has changed.

View Replies


ADVERTISEMENT

Forms :: Filtering Records Which Form Will Initially Display From Header File

May 30, 2015

I have a header/detail form working well. I use a Dsum to update a TOTAL field in the header - based on all the detail records for that Header.

However, I want to 'filter' or condition the records which the form will initially display from the Header file. I have a conditioning field (Invoice date) in the header which should 'block' it from display on the form. I may also desire to SORT the selected Headers record into a different sequence before display...

Looks like any "filtering" I try on the header table makes it difficult to go back and update the TOTAL in the Header?

View 2 Replies View Related

Survey Table Design, Have Searched Require Clarification.

Jan 12, 2007

Firstly hello, I'm new! :)

Sorry for creating yet another thread on table design/relationships for surveys, I did search just needed a bit of clarification.

From what I understand from my search the best way to create a basic db for surveys would be a question table, response options table, and response table, this is similar to a database I have implemented for a simple online surveying system. Now this works with Y/N, or even limited response questions.

However I can't get it to fit with my current project, and I don't think it will, but I thought I'd check to see if I'm being stupid.

I have a survey, 20ish questions expecting 200-250 responses.
The questions consist of a mixture of:

Yes/No answers
Select all that apply answers
Select one from list
Select one from list or select Other and enter it yourself
Text/Comments


Because of the wide range of values, some completely unique and the possible number of answers per question per survey it seems the above method (and that implemented in At Your Survey which i've had a look at) would not work.

I only need to store responses (not questions), so I'm proposing a main table with related tables for those questions that require it.

So in essence a flat file response table with each field being a different question, each record being a separate survey response.
For those questions with multiple answers a separate table, related to the main response response table.

Data entry will be twofold, a web version of the survey automatically recording responses and manual entry into custom form within access for those paper returns.

Reporting will be faily widespread with a lot of cross referencing.

I know this is not a reusable format, and it is pretty much a one time only project anyway, relatively short lived. But I'd still like to create the most efficient product I can, within the time constraints.

Any views or help would be much appreciated :)

View 1 Replies View Related

Forms :: Updating Fields Via Code Not Updating Table

Dec 16, 2014

I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'

Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub

When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.

View 1 Replies View Related

Access Initially Slow...

Aug 15, 2007

Hi Guys,

I've been searching for an answer for a long time, but i have a database with approx 100 forms, 100 tables (most linked to SQL Server) only a small proportion are front end tables, 50 or so queries, 20 or so modules.

Anyway the problem is, is that the database is slow, to open and to develop in, i have a decent system to develop on with a 3GHz CPU and 1 GHz RAM, but i dont get this problem with any of the other access databases i deal with.

It's kind of a lagging effect, then once it has been written into memory it's quicker... but why would that be different between the systems, as most the systems i deal with are fairly similar in size and in complexity.

For example when loading the logon screen, which is a simple unbound form, it seems to lag, there is 1 combo box which has a front end table as its row source, like 6 records. Then a username and password text boxes which are not bound to anything. Why should this be lagging? After like 2/3 secs its then ok, once ive logged in and then go back to this logon screen its fine, so i think it has something to do with memory.

Anyone else had anything similar happen to them and did they do anything to minimise this lagging effect?

Any ideas or comments are welcome,

Thanks.

View 5 Replies View Related

Forms :: Update Table That Is Recordsource For Combo Box That Is Updating That Table

Mar 29, 2013

I have a combo box (cboManifestNumber) that is based on the following table:

tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)

This table is related to:

tblTSDF
TsdfIDPK (autonumber PK)

I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.

View 2 Replies View Related

Front End Takes About 5 Minutes To Initially Load

Feb 5, 2007

Hi there

I have 3 PC's on my Network accessing a back end which is sitting on the main PC.

i.e.
Main PC has Front End and Back End
PC 2 has Front End and is linked to Main PC's Back End
PC 3 has Front End and is linked to Main PC's Back End

PC 2 and PC 3 take about 5 minutes to load the intial main form. Once it is loaded the performance is ok. I have a Gigabyte network so know it's not just a slow network.

I have had a look at many other threads and also read http://www.granite.ab.ca/access/performancefaq.htm but nothing has helped.

Any ideas about settings in say the options page that I may have set wrong.

FYI I have Default Open mode set to shared, Default record locking set to No Locks, and Open databases using record-level locking ticked. Not sure if these are correct or if there are other settings to consider here.

I have also found that once you open the initial form once, it then loads quickly if you close it and then open it again, but if you close the database front end and re-open it it then takes 5 minutes again.

The only answer I have at the moment is to never shut the darn thing...!

Thanks in advance for any suggestions.

Oliver

View 10 Replies View Related

General :: Updating Table With Forms

May 13, 2013

I have created a form and subform using form wizard where users could enter the data of a new order.In the form, there are some expressions where it does a computation of the fees that the company earned for each order. This is a percentage of the gross income.The subform expression formula updates the fee amount and net income automatically when the gross income is entered and fee percentage entered.

Is there a way to update the fee amount and net income which the expressions derived into a data field in a certain table?I actually need these information to be in the table too, as data as they are required by other users.Or is there a better way to do it?I am mainly using wizards and don't know how to use any SQL or VBA.

View 12 Replies View Related

Forms :: SQL With Textbox - Updating Table

Sep 23, 2014

I have an sql which is used to update a table

I am wanting to update the column1 with the value of an unbound texbox on a form

View 4 Replies View Related

Forms :: Updating Table From Cascading Combo Box(s) Fox?

Mar 25, 2013

updating my table when I use cascading combo boxes in my form.What is happening is that my table is being populated by the xxxxID column vice from the xxxxName column that is being used from that specific table.

here is my visual basic code that I am using to determine what the subsequent combo box will display.

Option Compare Database
Option Explicit
Private Sub cboPlanktonID_AfterUpdate()
' Set the Family combo box to be limited by the selected Plankton Type
Me.cboFamilyID.RowSource = "SELECT tblFamily.FamilyID, tblFamily.FamilyName FROM tblFamily " & _
" WHERE OrderID = " & Nz(Me.cboPlanktonID) & _
" ORDER BY FamilyName"

[code]...

Example of the Combo box Row Source is: SELECT [tblWaterbody].[WaterbodyID], [tblWaterbody].[WaterbodyName] FROM tblWaterbody;

My Control Source is PlanktonAnalysis.WaterbodyName

When I fill in the form with the data, The Waterbody name is visable for selection (example: I see "Lake Lillinonah" in the cascading combo box, But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody

PlanktonAnalysisIDWaterbodyName172
311411511677118397104113
tblWaterbodyWaterbodyIDWaterbodyName2Ashland Pond3Bantam Lake4Crystal Lake5Gardner Lake6Hungerford Park Pond7Lake Lillinonah8Lower Bolton Lake9Middle Bolton Lake10Pocotopaug Lake11West Thompson Reservoir

View 4 Replies View Related

Forms :: Updating Table From Unbound Textboxes

Aug 28, 2014

When a user selects a name from a combo box then 11 textboxes are populated with personal information.

What I am looking for is when the user edits the persons information (i.e. changes the persons phone number to a different number), how do I update the table with this information?

Is there a way to only update fields that have changed? or do I have to save all textboxes?

I have read about an "Update Query" and a SQL Update, but I did not think the two applied. I am sure that I am wrong, lol.

I tried the "Docmd.Runcommand acCmdSave (in the OnClick event cmd button) but it did not make any changes to the table.

This is what I put in the OnClick event:

Code:
Private Sub cmdSaveEdit_Click()
DoCmd.RunCommand acCmdSave
TextBoxLockDown
Me.Requery
End Sub

View 2 Replies View Related

Forms :: Prevent Combo Box From Updating Table

May 21, 2015

I have a combo box on my form which loads fields from a table and displays them using

Code:
SELECT DISTINCT table_team.team FROM table_team;

I then use

Code:
=[qry_showteamforedit]![team]

in the default value for the combobox to show the team which is saved in the current record.This is the qry_showteamforedit:

Code:
SELECT table_team.team
FROM table_team RIGHT JOIN table_staff_details ON table_team.ID = table_staff_details.team
WHERE table_team.ID = table_staff_details.team;

My problem is when I move through the records, if I change the selected value using the combo box it changes the actual value in the table from the one that was selected to the new one. If I was on record 1 and the teamid saved in there was 1 . It would display "team one" but if I changed that to "team two" it would change record one to say "team two" instead of "team one".I have been searching and found that this is because it is bound to the table so need to remove the text from Control Source, which when I do, breaks it, and it doesn't display the saved team.

what I would like it to do is display all the teams, but default to the one saved by using the id saved in the main table, but allow me to change this value. I would also like a second cascading combo box which will display a list of subteams dependent on what main team was selected and again, default to the values saved in the main table. I have managed to get cascading combo boxes working but combining them with my tables and queries is proving difficult. This is how my tables would be ( just showing the relevant fields)

Staff_table
ID Name teamID
1 Dave 1
2 Tom 1
3 Matt 2

team_table
ID team subteam
1 team1 subteam1
2 team1 subteam2
3 team1 subteam3
4 team2 subteam4

Is it is the subteams that will be unique I would like to save the subteam ID to the teamID field of the staff_table. that way i can retrieve the team and the subteam using the same ID.

View 2 Replies View Related

Forms :: Updating Cash Account Table Through A Form

Jul 14, 2013

How would I update my cash account account table through a form? I have just started working on access and am fairly new to it.

What I am trying to do is to update my master table with all the daily sales through a form. Would I need to write a query into my form?

View 2 Replies View Related

Forms :: Updating Calculated Dates From A Form To A Table

Mar 19, 2013

I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are

ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format

The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.

Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.

View 3 Replies View Related

Forms :: Updating Table With Calculated Field On A Form?

Apr 13, 2013

A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?

View 1 Replies View Related

Forms :: Updating Record With Form - Category Table

Feb 20, 2015

Here are my tables

Question Table
ID
Question
AnswerID
CategoryID

Answer Table
ID
Answer

Category Table
ID
Category

So my form shows :
Question, Answer, Category

And I can display any existing question and make updates to the question and answer, that works fine. BUT, what I want to do is update which category a question is in. If I change the Category in the form, it updates the 'Category' in the Category table. What I want it to do is update the CategoryID in the question table.

View 2 Replies View Related

Problem While Updating Table Data Using Forms In MS Access 2003

Feb 1, 2005

hi

i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.

rivate Sub Command10_Click()
Dim query As String

query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"

If (cn.State <> 1) Then
cn.Open "dsn=ABC", "", ""
End If
rs.Open query, cn, adOpenKeyset, adLockOptimistic
RESOURCEINFO.SetFocus
rs.Fields(0) = RESOURCEINFO
rs.Update

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
On Error GoTo Err_Command10_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

i am a beginner. any help would be greatly appreciated.

View 2 Replies View Related

Forms :: Updating A Table Field Using Unbound Text Boxes

Mar 26, 2013

I have a form that contains a combo box (cboEmployeeName) that pulls data from a query and populates three text boxes (Work Area, Last Name, First Name), This part works fine. Because the text boxes are being populated by the Combo box, they are not bound to the record source tblTrainingSchedule). I need the info that is in the text boxes to populate the respective fields in the record source.

I tested by adding "=tblTrainingSchedule!WorkArea=[cboEmployeeName].Column(3)" (column 3 is the work area) to the "after update" control but it does not populate the data.

View 4 Replies View Related

Forms :: Updating All Of User Controlled Fields In Customer Table

Sep 23, 2013

I need to create a form that automatically populates Dealer_ID in my Customers table when a user selects Dealership from a dropdown box.

The form is updating all of the user controlled fields in the Customer table as the user inputs the information. I have a separate Dealership table who's Primary Key is Dealer_ID where dealership information (including the dealer name) is stored. A one to many relationship is in place that connects Dealer_ID from the Dealership table to the same field in the Customer table. The user inputting the information will know what the dealership name is but will not know their ID. I need a solution that will allow the user to select a dealership name in the form and auto-populate the Dealer_ID field in the Customer table with the appropriate ID from the Dealership table.

View 1 Replies View Related

Searched Still Need Help :( Standard Dev

Aug 8, 2007

In one record (or row?) there I have 10 fields (columns), how can i find the standard deviation of those 10 numbers. Seems to only want to be able to do standard deviation if all data is in one field or column. I tried doing a query but same thing, if all the fields are across, it doesatn find the standard deviation of them. Please help this worthless access user. Thank you, I look foward to all the help I can get. As you can see, I still am struggling with even terminology. Excel is easy to me, access is a challenge to say the least.

View 3 Replies View Related

Forms :: Updating Record On Table - Enter Parameter Value Dialog Box Appearing?

May 16, 2015

I am having a problem updating a record on a table called BookInTable . The field I want to update is called Engineer and the record is found using the field Barcode. I have a form with 2 text boxes BarTxt and EngTxt as well as a button called SaveBtn. on the OnClick event of the SaveBtn I put the following code

Code:

Private Sub SaveBtn_Click()
If IsNull(Me![BarTxt]) Or (Me![BarTxt]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![BarTxt].SetFocus
Exit Sub
End If

DoCmd.RunSQL "Update BookInTable SET Engineer = " & Me!EngTxt & " WHERE BarCode ='" & Me![BarTxt] & "'"

End Sub

But this is bringing up the enter Parameter value dialog box for whatever I type into EngTxt. If I enter text into that box and then click okay it then updates to the record. how can I stop the from enter Parameter value dialog box appearing?

View 2 Replies View Related

Import Excel - I Already Searched And Still Need Help

Sep 27, 2005

I have used the search and it proved to be very helpful, as always. However, I still have an issue with my coding, it keeps trying to open my Excel file I have imported after I have closed it (or at least I think I have). Here is the code (copied right from another thread with a few tweaks):

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Sub Command0_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim oApp As Object

OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = Form.Hwnd
sFilter = "acSpreadsheetTypeExcel9 (*.xls)" & Chr(0) & "*.xls" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:"
OpenFile.lpstrTitle = "Select the Information to Import"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
Exit Sub
End If

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile

With oApp
.Visible = True
WrksheetName = "Import"
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel9, WrksheetName, OpenFile.lpstrFile, True
End With

oApp.Workbooks.Close
oApp.Quit
Set oApp = Nothing

End Sub

View 5 Replies View Related

Sorting Subforms (yes I Searched)

Dec 15, 2006

I did search the forums on this subject and can not figure out how to do what I want to do.

I have a form and subform setup. Basically by main form has contacts and my subform has log notes. It logs the date for every new record. It sorted fine up until yesterday when I ran an update query to change all the userIDs to UPPERCASE. Now it seems to be throwing the subforms in a random order.

I need the subform to order by Order date. Simple as that. I tried setting the property "Orderby" to Calls.Orderdate but it didn't work.

Thanks!

View 5 Replies View Related

Characters Before Searched String?

Dec 9, 2011

Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?

View 4 Replies View Related

Modules & VBA :: Set Focus On Searched Item

Jul 25, 2014

I'm trying to catalog these shelves for work. So someone can then search for the item and it will pull up the shelf its on. The shelf has alot on it so I want to set the focus on the item on the shelf. But I'm not too sure how to do that with my search button.

This is the code for my search button:

Private Sub cmdsearch_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).

[Code] .....

View 14 Replies View Related

I've Searched, But Just Not Finding What I Need. Exclude Weekends In Query

Oct 20, 2005

Hi,
I've been searching the forums, and I find a bunch of posts about the subject, but I just can't seem to really find what i need.

What I'm looking for is how to construct a query, that will exclude any entries in my table that might fall on a Saturday or Sunday.

What is the easiest way to do this?

I'm going to be using the results of this query in a Report and DAP.

Thanks
Chris.

View 3 Replies View Related







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