I have made a database for data entry, currently i have a challenge of getting it update and navigate.
On the form if the staff name is xyz it should only shows the records filled by xyz in form and navigate that records only. I am attaching the data base also....
I need to update data in a bunch of tables of a sql server database. The database has 300 tables which I have linked via odbc. I'm hoping there is a simple way, using vba, to loop through my linked sql server tables and determine which of them are views as opposed to tables.
View 1 Replies View RelatedI have a mainform set to data entry to add records to TABLE1. I inserted a subform that shows fields from TABLE2. I have a field in TABLE2, let's say TBL2FLD1(NUMBER). I want to add TBL1FLD2(NUMBER) from TABLE1 in the mainform to TBL2FLD1 and update said field in TABLE2.
My problem is:
(1) I get a #TYPE! error when I try to add the two fields, which I have tried many, many ways (adding them together in the Default Value; creating an unbound field to hold the calculation and then putting that field into the Default Value of TBL2FLD1; ETC), and
(2) I don't know how to get the subform to update TABLE2. It seems like the control gets stuck in the subform. Is there a way to accomplish this without coding it?
I have one field AccountName in customer table and another field AccountID.
In my form I would like to select from the combo box AccountName during data entry and then have the AccountID automatically update in the Account ID field.
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
Greetings.. I am attempting to teach myself Access but I am having trouble trying to figure out how to implement into my database the features I need my enduser to have.
Basically I am trying to create a Contacts database and I want the "form" that my end user enters data into to look like this (all examples are minimalist because I am looking for specific logical answers to solving my problems, not field suggestions):
Name: [textbox]
Phone: [textbox]
Email: [textbox]
Now I need to have Phone and Email each in their own tables, or in some way seperated so that I can retrieve a list of emails or phones without duplicates.
My initial attempt was to create the tables:
*key
NAMEtable
*Name
Phone (related to PHONEtable.Phone)
Email (related to EMAILtable.Email)
PHONEtable
*Phone
EMAILtable
*Email
Now the problem with that setup is that I cannot create a new entry into NAMEtable unless the specific Phone AND/OR Email are ALREADY listed inside their respective tables.
What I need to be able to do is give my user the ability to use a "form" with 3 fields, that will do the following:
Allow a new Name without a Phone or Email.
Allow a new Name WITH a Phone and/or an Email.
Allow a 2 different Names to have the same Email, but pointing to only 1 instance of that email in a different table.
(which it already does all if the Email and Phone already exist in their tables, I need it to be able to create new entrys into those tables from the form if they don't already exist). I also need to be able to enter in new Emails from a different form2 that dont have any relation to any persons in Name (which isn't a problem either at the moment since I just open the Email table and enter data).
I am unsure how to execute this first forms dilema though, I am assuming my database design is fine and it has something to do with some formality or code that I am missing to be able to do what I want. If so could someone help me out and make some suggestions? Or if my design is way off and there is a better way to be able to enter and store a Name, Email, and Phone from a form that relates back to a single contact and prevents duplicates then please let me know. Thanks
Also here is another post I made in a different forum but wasn't able to get quite the answers or help I was looking for.
"Name [textbox]
Email [textbox]
if the email the user is entering isnt already in the database it will not create a new row in email.email and then link contacts.email to that specific email.email...........
for example if
contacts NAME , EMAIL
row1 = bob , email.email row1
row2 = bobswife, email.email row1
email Email
row1 = bob@bob.com
then I open a form that will have 2 members, both pointing to the same email.
1 of 2
Name: [bob]
Email: [bob@bob.com]
2 of 2
Name: [bobswife]
Email: [bob@bob.com]
Ok, lets say I go to create a new member
I can do this just fine:
Try1
3 of 3
Name: [bobsson]
Email: [bob@bob.com]
What I can't do but need to be able to do is:
Try2
3 of 3
Name: [bobsson]
Email: [bobson@bob.com]
It will return an error stating that the email isnt in the email table. I need to know how to get Try2 to do what I want so that when I enter 3 of 3 my database looks like this:
contacts NAME , EMAIL
row1 = bob , email.email row1
row2 = bobswife, email.email row1
row3 = bobsson , email.email row2
email Email
row1 = bob@bob.com
row2 = bobsson@bob.com"
Anyways.. thanks again for any help.
I am having a problem where I have a form that I enter data into. Sometimes when I enter the information it will add a record to the table and sometimes it doesn't. It doesn't put the information in the table more times than it does.
I have tried to enter the information outside the form and it still does the same thing.
Do you have any suggestions of what I can do to fix this.
Thanks
I've just finished model of DB and it looks like this ( ERM.png )
Now I would like to insert some data into database though form, but if I for example add New contractor ( first name, last name ) with his location , it doesnt connect in table User-Location ... so it is not matched ...
I wish to designate unique customer codes in my database during data entry.
For example;
Liz Stimp = LS1
Liz Stamp = LS2
Lyne Small = LS3
Lipo Suction = LS4
I have successfully linked the first and surname using a "Left 1st letter" in the expression but I do not understand how to add a 'unique' number to the code to give me the result I am after.
Expression so far is .....Left([First Name],1)+Left([Surname],1)
I am making a database, where 10 to 20 users will be entering data. I have queries
1. I am planning to create a form with which user will be directly entering in the table, will that b right?
2. If 2 or more users will open form its own side does they all will be entering in same row number?
3. What will be the best of these, by insert query or directly to form?
I have a data entry form to add new records to an Access database file called Claims. An auto-incrementing sequence number (SeqNbr) needs to be kept PER YEAR. If the user enters a date the sequence number pertaining to the year of this date needs to be incremented. The first record within a new year of course takes value 1.Records can be added at random for different years.
A simple SQL-statement can be made to determine the new sequence number:SELECT max(Claims.SeqNbr) + 1 from Claims where year(this.value) = year(Claims.EventDate)...this.value meaning the value of the date control in which the user entered the date.I need to return the new sequence number to another field on the form in which also the COMPANY CODE, YYYY and MM from the EventDate, the new sequence number and the USER INITIALS are concatenated.
We have a table, JobRegister that people enter information on using a form, FrmOrderEntry
I would like to run a check when a serial number is entered in the field SerialNo to see if the same serial number exists where CompletionDate is null.
this would stop duplicate open orders being put on the system, or new orders being entered where the previous order hasn't been completed.
I need to alert the user of the database in case he/she enters a record that already exists in the database. If a person enters a key type and a serial number combination that already exists in the system and has status "issued", I need a pop up message to show up.
Am I missing some quotation marks somewhere in that DCount?
Private Sub SerialNumber_AfterUpdate()
If DCount("*", "tblIssuedKeys", "KeyType = '" & Me.KeyType & "' And "Status = 'Issued'" And SerialNumber = '" & Me.SerialNumber & "'") > 0 Then
MsgBox "This key has already been issued"
Cancel = True
End If
End Sub
I've got a data entry form bound to one table. The form has four buttons:
- Clear Fields
- Cancel
- Save and exit
- Save and add another (which should save the user input to the subform/table, clear the input fields, and allow the user to add another record)
I can't quite seem to get the "Save and add another" button to work. When I put some information in the input fields and click the button, it saves it to the subform/table perfectly, but when I try to do it again, it just edits the last record (the one just created).
How can I get that button to place the information from the input fields in a new record every time?
The _Click event for the button looks like this:
Code:
If Len(Me.field1 & Me.field2 & Me.field3) > 0 Then
Me.Refresh
btnClear_Click
DoCmd.Save
End If
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.
I have an Undo button : [btnUndo] and would like it hidden until someone starts to enter data, where it will become visible...
what code would I use? and where would
Code:
me.btnUndo.visible = true
be triggered?
I am trying to write code for a form. When the user enters the "Number of Payments" and the "1st payment date" it will fill the amount of the payment into a specific month.
For example: 1st payment date would be 10/24 the payments would be bi-weekly and the amount would be $50.00. The number of payments would be 4. I would need it to put $50.00 into a field called "October" $100.00 into a field called "November" and $50.00 into a field called "December". In excel I would do this simply by using a (date)+14 formula. I am sure that I need to do this with loops but where to even begin.
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
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ********
Public Function GetTargetType() As Variant
GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]")
End Function
************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ********
Private Sub Form_BeforeInsert(Cancel As Integer)
Me!target_group = GetTargetType()
'Forms!frmNmsConsumptionEntry!target_group = GetTargetType()
'[tblNmsConsumption.target_group] = GetTargetType()
End Sub
************************************************** ********
making sure I can insert this value once retrieved.
I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. [...].
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
So now, I am almost done with forms (at least I want to believe that). But I ran into an issue I would love to have your comment and different approach on:
The Database I create is projected on another, significantly bigger database! The one I create is a kinda light version for other field offices with only the information they need to have, and additional tables for them to gather their own data and track their own activities. This being said, the light database needs to be updated every month once by the big (mother version). Both DBs cannot be connected!!!
So we will send to the field offices altogether 5 tables in one mdb-file every month. These 5 tables exist with the same structure ( name, field data-types, etc.) in the light version.
I now want to make an automatic update option (by pressing a key and select the "update-file"), i.e. based on the the respective Primary Key of every table the entry in the smaller DB should be updated!
Update for me means the following three things (Assume big DB is A, small DB is B):
1) If Primary Key (PK) exists in both tables of A and B, update the rest of the fields in B belonging to that key based on the specific record of A
2) If PK does not exist in A but in B, delete record from the database B (case: record was deleted in A after last update)
3) If PK does not exist in B but in A, add the record to database B (case: record was added to database A after last update)
I would like to show you what I already found in the internet on solving task 1) but I cannot post links due to my status.
Both links basically describe how to update one table by another in importing or linking the update-source table in the current DB, create a query with both tables, link the PKs of both and then using the Update To Criteria [UpdateSourceTable].[Field] for each field that I want to update.
But I wanted to ask before I implemented that if I should make a complete different approach since I also have 2) and 3) as tasks....
I have a table called "Mov" and its columns are:
Code:
Number | Link | Name | Status
1899 | htto://example.com/code1 | code1 | Done
2 | htto://example.com/code23455 | code23455 | Done
3 | htto://example.com/code2343 | code2343 | Done
13500 | htto://example.com/code234cv | code234cv | Deleted
220 | htto://example.com/code234cv | code234cv | Null
400 | htto://example.com/code234cv | code234cv | Null
So I want a way to update Status of my rows according to numbers list. For example I want to update Status column for multiple numbers to become Done .
Simply I want to update "Null status" to become "Done" according to its numbers according to this list
Code:
1234
53
546
767
2135
6657
43
34
Something like this
I tried "update query" but I don't know how to use criteria to solve this problem. In Excel I did that by "conditional formatting duplicates" -with my number list which I wanted to update - Then "sort by highlighted color" then "fill copy" the status with the value...
I have data in all sorts of tables and databases that are linked together through different systems. I'd like to update some of the information automatically in the evenings or in the middle of the night without needing someone to push a button. Is there a way to do this?
View 9 Replies View RelatedI am working on a database project where I will Select a record on a subform
See the picture attached.
I know that using DAO recordset, i can update the checkbox one by one, but it become way too long when I have thousands of record...
May I know if there is a way to Select entire column of the subform?
I have an Access DB with a form that allows the user to select a new backend database. I can connect to the backend and then .refreshlinks but nothing on the form is updated. I have tried requiring the form but that doesn't do anything. I've tried loads of other things, refresh, recalc etc., but nothing updates the open form.
The only way I have managed to get it to work is to close the form and reopen it, then it shows the data from the newly linked backend database.
While it works, it doesn't look good but also there seems to be some problem with it because eventually it reports an error saying "cannot open any more databases" and when clicking OK comes back with "An error occurred while sending data to the OLE server (the application used to create the object" and a whole bunch of other messages.
I think it might have something to do with the fact that the form has a number of MS graphs open on it, but I'm not sure. Also, I can't track down a particular line of VBA code which causes this error.
how to update a form after changing the backend database.
I have a navigation form, a navigation subform, and then a subform. When I click the save button on the navigation subform, i need the data entered in two fields on the subform to save in a table. the subform is based on a query. This is what I currently have on the the button's onClick event:
Code:
Update data_tbl
Set [data_tbl].[Approved] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![CboAppd]
[data_tbl].[Comments] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![txtComments]
WHERE [data_tbl].[ID] = [Forms]![Navigationfrm]![Navigation subform].Form![Entry_subfrm].[Form]![ID]
I have a form that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub
and to all other fields:
Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.