Query Creating With Form Entry
Apr 10, 2013
I need to create a form, in which would be a query at the bottom.
I have table f.e. "A" in which is all data about cars.
I have table "B" in which is all data about customers.
I would like to create form "B" - which would add new customers, but at the bottom of that form I would like to see tables "A" filtered results.
Something like: In form B I create new customer. When I insert what car they want and how much they can pay, at the bottom generates query which would show all possible results from table "A".
With one customer I know how to do it. But when I insert second customer in the form, that query stops working while it doesn't know from which entry it should take the information. I don't know how to tell, that the query should take the data from the customer ID1 when I'm looking customer ID1, and from customer IDX - when I'm looking customer IDX.
I thought, perhaps it possible to create button with macro - when I press the button it copies all data from the current entry, it pastes in the form where is only one entry and works with query, than generates the query results which will be showed in the same entry that I pressed the button.
View Replies
ADVERTISEMENT
Sep 19, 2015
We have a client who is using an Access 2013 DB (Office 2013 Pro Plus) with the Windows 10 operating system. They are working on implementing the infrastructure for using Office 2013 under the Office 365 subscription. The Access DB they are using is setup as 2 .accdb files, one that contains the back end data, and the other the objects for the UI.
They will soon be using MS Surface Pro tablets for their field personnel, and want a form that can be used for data entry while in the field.
We talked about creating a form that could be used offline (basically a new FE and BE) and routine that would do data synchronization back to the network database when submitted by the field personnel.
From online research, it looks like this may be better solved with the use of Office 365, SharePoint, and Access Services. Unfortunately, since they haven't yet migrated to Office 365, we have no way to test it and need to determine if it is a viable option, or if we will need to do the "custom routine" option. Since they are planning to migrate to 365 anyway, if it works, it would obviously be a more cost effective solution for them.
Here are a few questions we have when considering this solution:
1) The Internet is not always available in the field, so the solution must be one that can run offline. Is this possible with 365?
2) They are working to get Sharepoint Services working soon. It looks like we will also need them to implement Access Services. What do SPS and Access Services look like, how do they interact, and what features do they offer and how can we leverage them?
3) What does the web-based Access application look like? What is the path to convert/migrate an Access 2013 DB to a web app? Does all development need to be done (or redone) in a new dev environment for 2013 web apps?
4) The "hydration" of an offline copy of the form is what we would need to distribute to an offline tablet device. What does this involve? Is a small footprint .accdw created? What happens after that when first accessed by the user? Is there a hydrated .accdb for the Front End and/or Back End?
5) If we must hydrate the entire application, we may need to implement security at that time. From our research, it appears that once the Access web app is published to SPS, the objects (lists, etc.) in SPS are lockable at the SPS level, and we may have to use that security layer instead of Access security, correct?
View 3 Replies
View Related
Dec 6, 2013
I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having a hierarchy, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will be a distinct record.
As fas as data entry is concerned, I would like the user to not have to re-enter the same information over and over when entering multiple species entries at the same sampling station, on the same date and so on up the hierarchy.
View 2 Replies
View Related
Nov 19, 2013
How do I create a form that will allow me to enter data into three different tables?
I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.
I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.
I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.
View 3 Replies
View Related
Oct 21, 2014
I am creating a form for data entry into my main table. It shows all the fields including a name, date, id number etc.
I am trying to put a subform on the form containing a query. The query has two filter parameters fed from two unbound boxes on the main form (date and name). Their purpose is to filter records on the query to just show those for one person on one day. It also calculates a duration based on a start and end time. The query results are ok.
I can't get the query to display properly in the subform.
Subform Properties/data/source object is set to the query name. I'm told i have to link the master fields and child fields but when I try I get the error 'can't build a link between unbound forms'. I thought all that was necessary to bind forms was for there to be a common field between the form and query? Both contain fields from the main table including date, name, start time and end time etc.
View 4 Replies
View Related
Nov 28, 2011
I have a form that lists records in a table. I would like to have a hyperlink beside each record that will open the data entry form and navigate to that specific record. Right now, the use can only open the form for all records and has to use the record navigation buttons to find the desired record.
View 1 Replies
View Related
Feb 21, 2007
I have been asked to add a building entry log (Session Log) for a gym members database I created 2 years ago, unfortunately having racked my brain for the last week I am no nearer a solution.
Background:
I am a novice with a little knowledge (Dangerous I know), the main structure of the database is Contact [Storing address and name], Payments [Storing membership info - renewal date etc] and Sessions [Storing session date & time] this sessions table was recently added as a stop gap measure to allow the staff to enter user sessions manually.
Problem:
System needs to log user as IN and record the current time on first entry of userID (5 digit number), then the second time userID is entered it must log user out and record the time.
Resources:
I have just a single laptop running windows 98SE, and a USB numeric keypad. I should be able to get hold of a second monitor if required, but beyond that it is a case of beg, borrow, or steal as my budget is ZERO.
Any help, or suggestions to get me on the right track would be greatly appreciated.
View 3 Replies
View Related
Nov 25, 2005
hi
i am trying to write a query to produce a descending recordset of photo_id but only one from each user e.g
if these are the top records
photo_id 150 m_name dave
photo_id 149 m_name dave
photo_id 148 m_name dave
photo_id 147 m_name john
photo_id 146 m_name john
photo_id 145 m_name fred
i want the query to produce this
photo_id 150 m_name dave
photo_id 147 m_name john
photo_id 145 m_name fred
and so on, there are other fields also but that gives you the idea i hope.
what i have come up with is this
SELECT MAX(FORUM_ALBUM.Photo_id) AS ID, FORUM_ALBUM.Photo_Name,FORUM_ALBUM_USERS.M_Name,FO RUM_ALBUM.Member_id
FROM FORUM_ALBUM, FORUM_ALBUM_USERS
WHERE FORUM_ALBUM.Member_id=FORUM_ALBUM_USERS.MEMBER_ID AND FORUM_ALBUM.Photo_Status=1
GROUP BY FORUM_ALBUM.Photo_id,FORUM_ALBUM.Photo_Name, FORUM_ALBUM_USERS.M_Name, FORUM_ALBUM.Member_id
ORDER BY FORUM_ALBUM.Photo_id DESC;
this gives me the records i want in the correct order but it gives multiple instances of each M_Name instead of just one record for each M_Name
i hope i have explained this clearly enough
thanks
Dave
View 2 Replies
View Related
Oct 1, 2012
I have a Comments field on a form. I would like to set it up so that multiple comments can be added and logged at different times by different users. I have attached a picture of what I am looking to do.
View 4 Replies
View Related
Jun 14, 2005
I am working on a jobs database where employees enter information where the job is being handed off to. I want to create a table showing the latest job entry by date. The jobs are listed by "Job Number" and when I try to create a table and remove the duplicate "Job Number" it does not always remove the oldest entries.
Any help would be appreciated.
View 1 Replies
View Related
Feb 13, 2015
How do I display a more meaningful message instead of the cryptic error about having to enter data into blah blah blah. How can I trap that error and provide them a more meaningful message about entering data. I have tried the following;
Main form name frmPatientRecords
Sub form name DentalRecords Subform
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Nz(tblPatientDetails!recordid, 0) = 0 Then
MsgBox ("sorry. Please complete the main record entry")
Parent.SetFocus
End If
End Sub
View 14 Replies
View Related
May 30, 2014
I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.
I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?
View 1 Replies
View Related
Mar 29, 2014
In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
View 2 Replies
View Related
Jan 24, 2015
Tried to find things but being a mix of a couple of different actions havn't been able to actually find it
So basically I found a awesome form somewhere that builds the SQL query based on all the users selections.
When you hit the create button it will save the query and update the sub data sheet below with your query results.
From here the part I'm lost on is getting the TOP x number of records and moving them to a new table, I wanted it to work on the 2 drop down boxes that are on the form. select your values and hit the button.
I'd imagine it would be a sub query that I'd use for the top x like I have in the past but I just can't seem to get it to work
Quick steps
1. create your query
2. check your data in the sub datasheet
3. decide the number of records to move to the new table
4. select the name
5. hit the button
This would copy the ban, xcv & dfs fields from the importeddata table to the moverecordshere table & also update persname in the moverecordshere table with the name selected in the form.
Sample db attached ....
View 1 Replies
View Related
Feb 11, 2015
I have 3 tables:
tblMain
BalanceID PK
GasID FK
ElecID FK
tblGas
GasID FK? not sure
BalanceID
tblElectric
ElecID FK? not sure
BalanceID
I created 1 query from all 3 tables, then created the form from that 1 query.Now when I enter data into the form and click to save it , it produces this error message:The Microsoft Database Engine cannot find a record in the tblGas with key matching fields 'tblMain_GasID.The tblGas does contain a field GasID, but does it mean that the field should be tblMain_GasID
View 12 Replies
View Related
Dec 26, 2014
I am creating a simple data entry form wherein the user will enter the product id and on change the product description and retail price must be displayed.
When the order quantity is entered, the total cost need to be calculated. I am able to do this using DLookUp and simple multiplication.
However, after doing the above, I need another row to appear so that I can accomplish the same for another product.
View 2 Replies
View Related
Jun 18, 2014
I have an "Returns" master form that contains two subforms. The subforms contain items that we are returning back into our inventory. The underlying queries in each subform show only those records where the "Return Date" is null. The query(s) works fine, except that if there are 3 items that need to be returned there are 3 records that show in the master Returns form. I tried the Totals option in the query but the I need that Return Date on the subform. I only want one Returns master form to show the 3 records (not 3 records of the same master form).
View 5 Replies
View Related
Aug 6, 2014
I have a database that makes use of standing orders. That means that if a client has a standing order to receive products during for example 4 time as year (quartely at the end of the month). to automate the new entry by copying an old entry in the database.
Let's say I have a client where we will have to send a product at the end of June, it will look at a field where the next send date is, and when it reaches 2 weeks for that date, to create a new entry in the database based on that entry. This way, it will pop-up in our open cases and we are aware of it and also will be visible in our report.
View 1 Replies
View Related
Sep 8, 2013
I am working on creating an access database for tracking physical assets linked to locations. I need to make a combo box list to show items other than the current location of the asset. Basically I need it to refer to last enery of the user and define the new possible entries. so we have a unique relationship between location and asset. The assets and location will always remain fixed and there is never going to be any addition. I am creating a web form so that it can be uploaded into sharepoint.
View 3 Replies
View Related
Mar 5, 2006
I have a data entry form and some records in it. What will I do so the entry form and records will not be edited when viewed?
View 2 Replies
View Related
Mar 5, 2006
I have a data entry form and some records in it. What will I do so the entry form and records will not be edited when viewed?
View 2 Replies
View Related
Nov 1, 2007
I have got a db when I enter a new client in the client form it saves it in the table but when I return to the form the client I have just entered is still there so I cant enter a new client it does'nt clear the text in the text box can anyone help
Thanks
View 2 Replies
View Related
Jul 13, 2005
I don't want the form to add the record if I put in a value in a field. I want it to add the record when I press a "Add information to a record"-command button. How can I do this?
My form is in data entry mode.
View 2 Replies
View Related
Dec 19, 2005
I need to design a query using fields from two tables, from which I am creating an autoform for data entry purposes. I have done this before with no problem. Now though, I cannot enter data - the option for new record is greyed out. Is there something simple I am not doing? Please help!
View 1 Replies
View Related
Aug 18, 2006
Say I have a form with three blank textboxes:
Name
Date
Description
And I have a table with those same fields: Name, Date, Description
How can I code a button to add a new row (record) from the form using the textboxes after they have been filled in?
I am familiar with the basic data entry mode, but for my purposes, it'd be best if I could do this without forcing the form to use the table as a Record Source.
Thanks so much in advance.
View 1 Replies
View Related
Sep 22, 2004
I want to achieve Access data updates to two tables through the use of a single form. Fields are identical in each table. I set things up this way in order to view the whole report or a single page of the report, and to this effect I am using the two tables. Can anyone help?
View 3 Replies
View Related