I 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?
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.
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 ?
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.
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?
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? ...
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)?
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.
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".
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
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...
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I am only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform.
I have been looking for days on the net for my listbox problem. It is there and found a few, even on this forum. but when i try the solutions mentioned i am in a total loss and do not know what to change to make it work for me.
I have a form named A/B Retriever with a record source qry input AB Bins Than i have a unbound combobox with row source qry input AB Bins. This populates 8 textboxes with B through I carton boxes, stored in a bin. The user selects a Bin location from the combobox and can put a "x" in a textbox to illustrate that the box is empty. This works perfect.
Underneath the input bin and box part i have 8 listboxes that shows a query that has counted the empty boxes with the corresponding bin location. this also works. but the question from users where, If i click on a, lets say empty B-box at Bin location 12A20, they want the combobox automatically focus on the combobox with the corresponding Bin location. This is a quick way for them to delete a empty box (remove the X).
I am trying to build a form that allows the user to INPUT stock as it arrives. This is simple with a product form that shows all the products in the table. I just go to the quantity field and change it.
But, I want to create a 'search' function in the field. I want to allow the user to type in the Product number. Then the Product name, Product Price and Quantity is AUTOMATICALLY displayed.
The user can then change this value (items in stock).
Hi, I need help on cascading combo boxes, in my form
In my example DB i have 3 tables(when i find a soloution i will implement it on a larger scale database which will have around 15 tables). I need to create reports of these tables. The problem was that i need to filter the data before i create the report by 2 fields. If i was to do this the straight forward way i would have 3 tbls, 3 qrys and 3 reports (which on my final database would mean 15 of everything.)
So i've decided to use a form driven system to reduce the number of forms and querys i have in the DB. Using a form based system i should have 3 Tables (called 1, 2 and 3), 1 Query (called query2), 1 Form (called withselect) and 1 report (called rptquery1).
Now to the system.
The form should have 3 combo boxes. 1 at the top to select the table i want to query. Then 2 others to select the fields by which to filter. the form should also have 2 buttons, 1 torun the report one to return reults in a table to edit the data if needed.
This is the current problem i am having
In the forms 1st combo box i have managed to list all my 3 tables (thanks to FancyPrairie) using the following code:
SELECT MSysObjects.Name, MSysObjects.Type FROM MSysObjects WHERE (Left$([Name],4)<>"MSys") AND (Left$([Name],4)<>"USys") and (Left$([Name],1)<>"~") and (MSysObjects.Type=1) ORDER BY MSysObjects.Name;
But i cant select any thing in the following 2 comboboxes. At the moment im tryin to use this code in the record source of the 2nd and 3rd combo boxes
SELECT * FROM YourTable WHERE (YourTable.TableName=[Forms]![withselect]![Combo6]);
Attached is the latest version of the DB.
Im getting the error:
The record source 'SELECT * FROM YourTable WHERE (YourTable.TableName=[Forms]![withselect]![Combo6]);' specified on this form or report does not exist.
Let's say, I'm updating a record, and I have a combo-box that says transfer and another combo-box that states "where to?" - or words to those effect.
Each record has a field for credit value and a field for debit value.
When I've finished entering the current record, I want to run code that fires the event, so, copies all the data from one record, to create a new record, BUT...
1) changes the "where to" from the old record to "From" in the new record. 2) changes either the credit or debit value, to the opposite in the new record?
I would like to do this from a sub-form. I can copy a whole record, no problem, but can't seem to change field values.
I have 1 combo box contains 2 columns look-up directly from the properties(Not VBA)
Now i want to select record from the list
Example: i want to select PM-1234-1111 so i dont want to type starting letter PM to select but i want to type 234 or 123 or somewhere in the middle or end to filter that contained text in all the items is it possible?
it's easy to set up a form with a combobox that will list all records and moves the user to the selected record. But I can't figure out how to just show a selection of the records in this combobox, instead of all records.
So, to use the example used in most online tutorials: a drop down menu shows you all the names in a customer database. However, I'd like to have instead a dropdown menu with an overview of all surnames in the database and that I move to the first record with the selected surname.
I have a problem with a DB I just designed. It has a combobox that is populated from a query, and it works great. However, the problem is that when I enter a new record the combobox is not being cleared, and is "holding" the data from the previous record. How can I get it to "reset"?
A continuous form with fields Operation bounded integer, LibelBx unbounded combobox string with two columns as Sorter (Int), Phrase(str).
A table TargetTbl with field Operation(Int). A table TextArrayTbl with fields Phrase(str), Sorter(Int).
Relationship between TargetTbl and TextArrayTbl is one to many joined on Operation = Sorter.
I am trying to display the form. RecordSource = TargetTbl. LibelBx.RowSource = TextArrayTblQy.
The idea is to display every record from TargetTbl with Operation equal to Sorter from TextArrayTbl. The LibelBx combobox should display the Sorter and Phrase.
Code: TextArrayTblQy = "SELECT TargetTblQuery.Sorter, TargetTblQuery.Phrase FROM TargetTblQuery, TextArrayTbl, TargetTbl WHERE (([TargetTblQuery].[Sorter]=[TargetTbl].[Operation]));"
Code: TargetTblQuery = "SELECT TextArrayTbl.Sorter, TextArrayTbl.Phrase FROM TargetTbl, TextArrayTbl WHERE (((TextArrayTbl.Sorter)=[TargetTbl].[Operation]));"
I managed to get the TexArrayTbl displayed in LibelBx combobox, but it displays all the records whereas I want it to display only the ones with Sorter = Operation.
I have a form with subforms, where the main form's source is a table called tblCharts. Within this table there is a field called ID that is autonumbered.
So I created a combo box that's row source is a query that selects the ID field from table charts and two other descriptive fields to make navigation easier. Limit List is set to yes.
The problem comes up with code that I've used multiple times, but not with an autonumber. The code typicallly looks like this when I'm searching on a text field.
Code: Sub cboPatientSelection_AfterUpdate () Dim rs As DAO.Recordset If Not IsNull(Me.cboPatientSelection) Then If Me.Dirty Then Me.Dirty = False
[Code] ....
This worked at first but then 3070 runtime error came up, the form started messing up. So I made this code
Code: Sub cboPatientSelection_AfterUpdate () Dim rs As DAO.Recordset If Not IsNull(Me.cboPatientSelection) Then If Me.Dirty Then Me.Dirty = False
[code].....
This code worked at first, so I decided to make a copy of my form called Copy Of frmCharts. After this the code stopped working on the initial frmCharts but now works on the copy.
I also received other errors besides 2070. I know what error 3070 is but I can't figure out why it's not working on this form. I closed and opened the form a few times and it happened to start working. Very frustrating when I'm trying to make the system reliable for others to use
The code only seems to do this when I enter design view then switch back to form view.
I have a database with a Supply Receipt Form. The first field in the form is a combobox for the user to select a material/supply. I'd like for the form to open to a new record every time. When I use:
DoCmd.GoToRecord , , acNewRec
in the Form On Open property, I lose the ability to select the material/supply. Same thing happens when I set the Data Entry property to Yes.
I have a continuous subform of 'static data' whose record source is a SELECT query across multiple linked tables.
Most of the fields are locked and purely there for information purposes but I need to be able to allow end users to change one particular field in each record if they need to - choosing a value from a predetermined list (i.e. one of the tables)
Usually, when I need to do this, I add a command button to the subform and use that to open a separate pop-up form specific to that record, from which the user can make whatever changes they like and then update the record. So in this scenario, a simple unbound combobox linked to that table, on that separate form, would work quite easily.
But I would prefer if users could make their changes directly from the subform without (yet another) pop-up form required each time, to make managing these records more fluid and less time-consuming.
So my thought was to use a bound textbox in the continuous subform (to take the existing value for each record), hide it and overlap it with a visible unbound combobox whose row source is the table of available options and whose default value is driven by the hidden bound textbox. And then to use the AfterUpdate event of the combobox to run the UPDATE query on the record in question.
However, while the combobox is getting the correct default value and the correct list items, I can't make a selection from it. Now I am aware there are 'issues' with using a combobox in a continuous subform but I was hoping I could circumvent them by not binding it.
