I have a form which my client likes but he doesn't like to use a search button.
The two indexed fields are EMPLNO and LASTNAME.
By entering the employee number into the form and/or by entering the lastname onto the form you would get the record for that employee onto the form.
Its probably covered in the archive but I don't know what to look for.
Any suggestions?
Hi,
I am new to access programming. I want to do the following but don't know how :-
I have a form which is full of text boxes for people to enter data. I want them to enter the relevant data into those textbox's and then to click a SAVE button. Only when the SAVE button is pressed do I want the contents of the text boxes to go into the relevant fields in a table, i.e. they are all unbound.
Can anyone tell me how this is done please and possibly give an example code?
Thanks
I am creating a log in form, it checks the user name which is unique. if the user has entered a password, it shows only one password text box, they enter the password, if it is correct they enter the database, otherwise they return to the text box.
But if they have not entered a password before the form opens with two text boxes, one for the password and one to confirm the password is typed correct, if the are different a message box shows telling them that they are different, now is where i having problems, when they have typed the two passwords and they are correct i want them to save this password in the same record "Password" as the selected "username" record, I can find the "username" record by doing a Dlookup, easy, but i am stuck how to then save the password from the text box where the selected username record is.
My table "staff" has fields of "IDStaff", "FirstName", "Surname", "Password", "Username".
I have a unbounded form which has all the fields same as the table. Now I want to search a row from the table with the Ticket number and First Name. After retrieving all the fields instead of using Update query I just want to add it as a new record.
View 1 Replies View RelatedI have a form [tblStock]with a combobox bound to the Primary key [ContactID] of a table [tblContacts].
Also on the form is a subform[subContacts] bound to a query that is based on the value of the combobox
on the form. So that the details of the combobox[ContactID] is shown in the subform [subContacts].
The trouble is that i dont seem to be able to add a new ContactID in the combobox and fill in the rest
of the details in the subform.
Problem 1 is that the focus moves to a textbox that i have on the form[tblStock] when
i enter the first character into the combobox.
Problem 2 is that when i continue to click into the combobox and type the ContactID that
i have typed does not get ammended to the table[tblContacts].
I am very new to Access, it probably shows, but this problem is driving me mad....Anyone?
I have a subform that collects the following data to put in a record in tblClass:
StudentID, Trimester, SubcatID, WorkGrade, SkillGrade
Before the record is written for the first time OR edited/updated to the table, I want to search tblClass and determine if the new/updated info will create a duplicate record.
In this specific case, a duplicate record will be defiened by a record where the only fields being considered would be StudentID, Trimester and SubcatID. The fields WorkGrade and SkillGrade should not be considered.
The code I came up with was the following and it was put in the BeforeUpdate:
Dim conn As ADODB.Connection 'Connection Object
Dim rst As ADODB.Recordset 'Recordset Object
Dim strSQL As String 'SQL statement for open statement
' Create object variables
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
' Create sql to search for records in tblClass that match
'studentID, Trimester, and Subcatagory in the form record being added
strSQL = "SELECT * " & _
"FROM tblClass" & _
"WHERE fldStudentID = " & StudentID & " AND " & _
"fldTrimester = '" & Trimester & "' AND " & _
"fldSubcatID = " & SubCatID & ";"
' Open recordset
rst.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rst.RecordCount >= 1 Then
' record already exists in tblClass
msgbox "Record already exists!",, "Duplicate Record Error"
Me.cboSubcatID.SetFocus
Cancel = True
End If
' Close and disassociate object variables
rst.Close
conn.Close
Set conn = Nothing
Set rst = Nothing
This code worked great except when I went to edit an existing record. When I went to change a grade (WorkGrade or SkillGrade) on an existing record, it told me that I could not enter the record because the record already existed (ie, the record I had open and was editing). I am not sure if it is my code that I need to edit or if it is the placement of the code I need to change.
Any suggestions would be great.
Hello! :rolleyes:
Just need a little extra help :(
I have set up a form for the user to fill in a new record and used a command button for it to save. This all works ok.
The problem I have is when the form is opened, a record is shown in each of the fields as opposed to a blank one. The user can still enter data but they need to delete the stuff that is already in the box.
The user then, will have to press the little star at the bottom.
I have tried going into to design view, and typing in "Unbound" but it came up with #name? ...
can anyone help me ?
Shellie x
Hi There,
I have built a very basic DB and am running it alongside our existing Excel system of recording customer data.
I have transferred a lot of data from the Excel spreadsheet to the DB Table using Cut & Paste. For all future incoming data is there any way I can just enter it onto the Excel spreadsheet and it is automatically transferred to the DB table or am i consigned to entering two lots of the same data?
Best Regards
Keith:(
What is the best way to open a linked subform using a button while in a newly created record?
I thought a simple
Code:
docmd.save
command would do (either in the "current record" or the "after update" event)
The program I am working with has an option to add a new record to another table using a button. Not all the records, even new ones, need to be appended.
The append query works fine in all situations but one--when a new record is entered and saved, the append query button returns 0 records to be appended. However, if you go to a different record and come back to the one just added in the main table, the append query works fine. The query uses a TempVar to select only the record being seen at the time. The TempVar is declared prior to attempting to append the record to the other table.
Is there a way to make a just entered record act like its been around for a bit (well at least to save it by changing record to another and back)?
is there any way for a table to refresh on its own after entering a new record?
i tried me.requery but it doesn't seems to work.
Background info:
Split database
Back end on network
Front end on individual machines
I have a main menu form that opens up when opening Access.What I'm thinking is have some vba in the OnLoad Event of the main menu that Grabs the User and Time and track this to a table.When the database closes(Is there an OnDatabaseClose Event?), I'd like to track the User and time as well.
LogID(Autonumber)
User(text)
TimeIn(Date)
TimeOut(Date)
I have a form with a sub-form
eg
Purchase Order with main details on (Po Number, Supplier etc)
with a sub form carrying the line items to be ordered.
Table PO
Form PO
Table POSUB
Form POSUB
When entering main order details into Form PO, why do the fields in the related table(Table PO) immediately get populated when the the focus gets transfered to the sub-form (Form POSUB). with users quiting the database illegally (not by the cancel records button) the result is unwanted records in the Table PO.
What I want to do is complete the input fields in the main and sub forms without any records being commited to the tables until the "Save Record" button is pressed.
Thankyou
I've been working on a database (attached) for a health trust. I think the relationships are right but I'm having a problem entering data. The subform shows the correct data but I can't figure out how to enter data using a form.
Can someone point me to a tutorial - I have searched the forums - honest!!
I have a db with the following fields: ID, Customer first name, customer last name, account number, date, time, score1, score 2.
My problem is this: We are running a promotion in which the customers receive a score. This score must be entered correctly as we are highly regulated, however the users are constantly fat fingering or miss typing the data. I want to force the score 1 and score 2 fields to match before the record can be saved.
Any suggestions on this would be greatly appreciated; I have to go in every night to correct these errors manually, which defeats the purpose of this db.
Is there any way for a table to refresh on its own after entering a new record?
I tried me.requery but it doesn't seems to work...
I've designed a form to enter several items at the same time, eg, I want to enter aeveral company names on one form. The trouble is that when I enter data into one box, it appears in all the other boxes with the same field names, not allowing multiple data, is there a way around this?
View 2 Replies View Relatedhi, i have a query which when the user selects a value in a combo box it pulls up the other details of the item, such as price.
i have noticed though that if i try and enter a new item into the list, through the combo on the form and then enter a price, it gives me error 3101 (something to do with not being able to find the record.
i assume this is because it is trying to locate the chosen item of data within the main table and pull up a price.
is there any way around this.
jjames
I have a form with two fields, EMPLNO and SURNAME.
By entering the employee number in EMPLNO or by entering the name in SURNAME I wish to get the correct record onto the form.
This is probably covered in the archive, but I'm not sure what to look for.
Any suggestions.
Hello,
Below is the main data entry form of my application. We disrtibute a product called MC Cloth to Shops to display. After a month we visit again and take stock of products sold, replenish and the shop pays for the products sold.
the Database keeps a record of the shop, products displayed, refilled, sold and respective payments.
The dtabase and the form is loaded in Handheld (PDAs) by the sales people who enter data during the visit and then synchronise with a master on return
http://affiliatesexcel.com/MC_main_form.jpg
I need to sum up values in one field for example MC Refill from the first record till the new record and show it in another field, for example MC Refill Total
Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).
However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty.
NameCust_IDRecordNumAddress
whs01ggggggggg
02qqqqqqq
03mmmmm
04nnnnnnnnn
05ooooooo
06pppppp
Kickstart08xyz
09898989898
012mmp
013qty
This makes the search function impossible based on the NAME value.
As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)
Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down .
(I have tried a mehod which entered the values for
all records but this clutter up the Pull down with SAME
Name for repeated records making the PULL DOWN unusable.)
I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.
+++++++++++
Code:
Private Sub Name_Combo_AfterUpdate()
' This procedure tries to find the matching product's record.
' If the matching record is found, the procedure goes to it.
' If the record isn't found, the focus stays on the current record.
Dim Criteria As String ' This is the argument to the FindFirst method.
Dim MyRS As Recordset ' Recordset used to search.
Dim ComboName As String ' The name of the company to search for.
Const IDYES = 6
Set MyRS = Me.RecordsetClone
' Build the criteria.
ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34)
Criteria = "[Name]=" & ComboName
' Perform the search.
MyRS.FindLast Criteria
If MyRS.NoMatch Then
Response = MsgBox("Could not find the Supplier Name: " & ComboName & " Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48)
If Response = IDYES Then
MyRS.AddNew ' Create new record.
MyRS("Name") = Screen.ActiveControl
MyRS.Update ' Save changes.
MyRS.Move 0, MyRS.LastModified ' Go to new record
Me.Bookmark = MyRS.Bookmark ' Go to new record
Else
GoTo Endsub
End If
Else
MyRS.AddNew ' Create new record.
MyRS("Name") = Screen.ActiveControl
MyRS("Cust_ID") = MyRS("Name")
MyRS.Update ' Save changes.
MyRS.Move 0, MyRS.LastModified ' Go to new record
Me.Bookmark = MyRS.Bookmark ' Go to new record
'Me.Bookmark = MyRS.Bookmark
Dim recNo As Long
' for this to work there cannot be any RecordNumber with a value of 0
' it finds the highest record number for the name in the combo box
' and returns 0 and exits if no record found.
recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0)
Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'"
If recNo = 0 Then
Exit Sub
End If
Me.Text90.SetFocus
DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
End If
Endsub:
MyRS.Close
End Sub
+++++++++++++++++++++++
The question is is there an easier way to
achieve the summing function ?
Any help is greatly appreciated as always.
--------------------------------------------------------------------------------
I have made my first form and I did not to bad (?). I am thinking there is a faster way to enter data into my drop down box. I have set the tabs in the order I like but I have to tab to the next dropbox, then double click to open the box, then double click on my choice then double click on the next one. Is there a quicker way to go through 25 dropboxs?
View 6 Replies View RelatedI have a form into which I scan a serial number in one of its text boxes, I can then select search and am presented with a report relating to that serial number. All simple so far. Now for the dilemma... The barcode I scan consists of 15 characters like so, 53423PP98765432, numbers-PP-numbers.. the problem I have is that I only need the numbers after the PP's, in other words the last 8 digits. My question is, is there something I could do to make the text box omit the first 7 characters automatically, leaving me with the 8 I need, instead of me having to curser into the middle and manually delete the first 7 characters. I only need the last 8 because of the link with another database that only uses the last 8 digits.
Also, on the device I scan, there are 2 other barcodes, above and below the one I need to scan. If I scan one of the others by mistake, I have to highlight and delete the results to try and scan the middle barcode. The other barcodes also have a different length to the one I need. So is it possible to write some code that says, ok, you have scanned a barcode with 10 or 12 digits, we don't want either of those, so deletes it for me to try again, but then recognizes the 15 digit barcode and auto deletes the first 7 characters as mentioned above.
I'm using Microsoft Access 2007
I have three tables: the 1st is for product's identification, the 2nd is for registered products, and the 3rd is for under-registration products
and the primary key for the three tables is the Registration Number
and there is a one to one relationship between the product identification and the registered products
and a one to one relationship between the product identification and the under registration products
What I want to do is to make an append query to move the under registration product to the registered product when its process is over.
One of my problems is with the primary key for the under registration products table, as they only get their registration number when the process is over. so how can I enter data into this table without the value of the primary key ?
In trying to respond to another thread, I have run into something that is confounding me (or maybe I'm just getting dense).
We have a subform. One field has an event on DblClick to launch a search form. When the user identifies the target, he/she clicks a button on the subform. This pushes the appropriate value into a field on the original subform using VBA code and closes the search form. This all works fine.
The behaviour that is driving me bugging is when the user clicks on a new record (i.e. new line) on the subform, we would like to automatically generate the next record (E.g. when you type in a field of a record with autonumber in datasheet mode, Access automatically generates the next record). Currently this doesn't happen - Access generates the PK for the record being modified, but doesn't generate the view of the next record.
What really confuses me is that I have created similar looking example in which this works just fine. I can't figure out which of the differences between the two samples is causing this behaving.
Also, typing information into the field on the subform does cause the next record to be generated. It is just doing this via code that works in one case but not another.
I have narrowed it down to the actual subform. Even as a standalone form the form exhibits the same behaviour.
For reference, the original thread is
http://www.access-programmers.co.uk/forums/showthread.php?t=99457
Any suggestions?
-grommit
I am having a problem with my Contacts Database. I have attached it for your review.
My main form entitled "frmContact", is a form to enter a new contact. I have a Notes subform, and I want to be able to enter multiple notes for one person. I have a note for John Doe, and tried to enter a new note for him and got this error message:
"The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'tblcontact"."
Why is it doing this? I tihnk I have the relationships correct, and everything...
Is it possible in Access to create one input form that includes fields from different tables.
I want to create a single form that dispenses input fields to separate tables, I don't see anything to make this happen. I know you can retrieve data from separate tables utilizing querys. But is it possible to input data into a single form to multiple tables ?