Forms :: Combo Record Selector Using Either ID Or Username
Jul 19, 2013
My problem is related to an Access database, where an auto complete Combo box is used to select a client record by entering the ID number or the clients name. EG: 14034 or Bloggs,Fred. This problem has me puzzled (not difficult to do).I have seen this in a functioning DB. I can see that maybe the ID (a long integer) would have to be stored or converted to a string on the fly.
I am currently using Access 2007 but 2010 and 2013 are available (prefer 2007 as MS keep moving things around ).I am currently selecting a client record using either an ID select Combo or a ClientName Combo and works very well. But, after seeing it done in one Combo box, it just seems so elegant.
Combo2 - Record selector that works, either shows a list of facilities or list of physicians. Macro SearchForRecord - Where Condition ="[fac_id] = " & Str(Nz([Screen].[ActiveControl].[Column](0),0))
Would like it to be
="[fac_id] = " & Str(Nz([Screen].[ActiveControl].[Column](0),0)) AND [referral_type] = Me!Combo2
When moving from a subform back to the mainform and requerying one of the mainform controls, the record selector of the subform moves back to its first record. ...
GoToRecord (,,Next) '(in the subform-this is correct) SelectObject Object Type Form Object Name pv '(go back to the main form) In Database Window No GoToControl (SubTot)
Requery (SubTot)If I remove the Requery, the record selector remains where it is supposed to be. The SubTot data source does include the aggregate dsum value of the subform's underlying table. What is strange is that this has been running properly for many years until it was upgraded from .mdb to .accdb format. Is there an easy way to make the record selector stay put?
I am using Access 2013. I have a form with 2 subforms, each time the form is opened, the first record on both subforms are selected. Is there a way to turn off the record selector so no records are selected when the form opens?
I am having a difficult time figuring out how to have a table add a new record that includes the Windows username of the user that clicks the button on my form. The function is in place that pulls the Windows username:
Code: Public Function getWinUser() As String getWinUser = Environ("UserName") End Function
I then call the function during the button click by simply adding getWinUser. Unfortunately, all that occurs is that the same record gets updated instead of creating a new record.
In the zip-file attached below is my "problem" database. (to see the problem open frmLedenArtikels) On top of the form just select a name from the combo box, the subform then lists the items that person has bought on a certain date. So very easy form.
The problem is the following. When I try to add items to the subform, the record selector in the subform always automatically jumps back to the first record in the subform. this is very annoying and I have totally no clue why this is happening.
Does anyone know how to remove the "Record ID" from the record selector? I want to be able to traverse from one record to the next, however I do not want the field representing what record it is on showing up ( if that makes any sense).
Hi All, I have a form that when loads, runs some VBA on the "On Current" event. Some of that code, fills in text boxes, with concatenated (?) strings. So every time I use the record selector and get to the end of all the records, the "On Current" fires adds the concatenated string (even if it's empty), and that then becomes a new record! Is there some way i can prevent this, but still keep the "On Current" event, some type of logic I could run before the concatenations etc.
How do I remove a record selector from a subform? I went to the actual form for the subform and went into design view. I changed the record selector to NO and it is still there.
I have a table with fields ID, NAME, ITEM, QUANTITY and DATE_IN. I have a query "qry_Logged_In" based on the table. I then have a form based on the query.
I created a combo box as a "go to record" selector and used the following Row Source:
SELECT DISTINCTROW qry_Logged_In.ID, qry_Logged_In.NAME, qry_Logged_In.ITEM, qry_Logged_In.QUANTITY, qry_Logged_In.DATE_IN FROM qry_Logged_In ORDER BY [ID];
On the After Update proceudre I have the following code:
Sub Combo135_AfterUpdate() ' Find the record that matches the control. Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo135] Me.Bookmark = Me.RecordsetClone.Bookmark End Sub
It works fine, but one thing I would like is that as I toggle through records using the record selector (forward/backward) buttons, I would like for the record number in my combo box to also change keeping in sync with the record number. Unfortunately, it currently stays to the number I last selected in the combo box.
Any suggestions on how to make the record number showing in my combo box to change with the record number?
Hi, In Access 2007, I don't seem to be able to scan through a few records by holding down the advance record arrow in tables liked I used to?! Am I missing something here, it worked in the same way in all other versions, the same as if you use the mouse wheel, it should run through each record in turn.
Is there a way to change this? I've been through all the settings I can think of...
Hi Can anyone confirm (or otherwise) that you can't set the record selector property to 'no' in a datasheet view form? (well, you can set it to 'no' but it seems to have no effect)
I need the dB to automatically pick up the username of the user logged on to the PC. Thus if I'm logged on to the PC as FLESTER, this username wil populate a database field.
How do you get Access to pick up th OS username currently logged in to the database? For example I'm logged on to the PC as FLESTER and I wnat that recorded in the dB.
I have a form where there are many users to enter or update data. I need to capture the username who created a record , then , on form load , I want system to check for username if it is same as the creator of a record , then allow update , otherwise don’t allow update , but allow only adding new records. How can I do this
I was wanting to know if there is a way to Add the Username to a table as a default value when a new record is added. I know you can add =Date() to get the date. Is there a simple way to get the ID of the person logged into Window?
I am trying to record the current windows username in an audit field. But all i can get is the Username Admin?
Dim MyForm As Form, C As Control, xName As String Set MyForm = Screen.ActiveForm 'Set date and current user if form has been updated. MyForm!Audit = MyForm!Audit & Chr(13) & Chr(10) & _ "Changes made on " & Now & Date & " by " & CurrentUser() & ";"
I am trying to match if a user has already started a record using the date and username. I have written the following code but I keep getting a error 94 about null. I know its an issue involving the date part. why this isn't working.
Code: DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#"))) The full code
Code:
Dim Date2 As Date Date2 = Date If (Not IsNull(DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#"))) Then lngEmployeeID = DLookup("TimeSheetID", "TimeSheet", "EmployeeID=" & Nz(Me.ListEmploy.Value) & " AND TimeSheetDate=#" & Date2 & "#") DoCmd.OpenForm "frmTimeSheetMain", , , "TimeSheetID=" & Nz(lngEmployeeID), , , "NoTimeSheetID"
I have a form that currently uses a "catch all" table for listing available equipment to choose from for an equipment field. I call it tblEquipment. What I want to do is to make it so when I type a name in (1 of 35) in one field of the current record, the record source for the equipment field immediately looks at a different table that has equipment available only for that name. To do this I plan on making 35 different tables with limited data originally found in tblEquipment. I would call these tblEquipment1, tblEquipment2, etc. I do not use a sub form, nor do I want to.
So my questions are:
1) can this be done 2)If it can be done, how can I do it?
I have a problem with my database I have a combo box that will search for my record. Actually its working I input the specific number it goes to the specific record. But I want, if there no existing record in my database it will display a Messagebox that "No record Found" I try to put a code in a macro builder in a after update property field but nothing happened.
Expression code that it will display the msgbox if there's no record found.
the given code from macro builder is attached. I try to have an if else statement but I dont know how to not equal that giver conditional expression.
I have a module that pulls the user name in the firstname.lastname format. This is the module (module name is fosUserName):
Code:
Public Function GetUser() As String Dim WNet As Object Set WNet = CreateObject("WScript.Network") GetUser = WNet.UserName End Function
I what to add a a text box on the welcome screen that says something along the lines of Welcome firstname (from the firstname.lastname that the code above pulls) what would you like to do? I need some code that will get me all the characters left of the period that separates the firstname from the lastname.
I have an error message or debug message in my code in username & password log in.. it has an error in if else statement
empname is a cobobox empass is a txtbox logempID is a autonum I.D usrnmpass is a table name
there's an specific record in a table of usrnmpass showing in the attachment
this is my code....
Private Sub Command1_Click() If IsNull(empname) Or empname = "" Then MsgBox "You must enter a User Name.", vbOKOnly, "Required Data" empname.SetFocus Exit Sub End If