Programmatically Update a Form's Combo Box Default Value and SQL Record Source

I have one Sales Activity form that I use for data entry and for viewing all
records by Sales Person (EmployeeID).  Without having to have multiple
instances of this form, I want to accomplish the below; based on which
EmployeeID (sales person) opens the form in either mode:

1. For the data entry mode – I want the Combo Box’s Default Value to change,
based on my user (EmployeeID);
2. Show All Records mode – I want the SQL record source to filter on that
sales person’s records (EmployeeID)

I’ve got some code for passwords, so I hope I can utilize that part of it; I
just do not know how to do the above two steps.

Thanks.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
Desilu
1/24/2008 5:06:59 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
882 Views

Similar Articles

[PageSpeed] 10

Hi Desilu,

Here is some code that will change the RecordSource for the Combo box.

Private Sub Private Sub Form_Load()

    ' Create a variable to hold the Employee ID 
    Dim lngEmpID As Long

   lngEmpID = EmployeeLogonID(without more information I can't tell you what 
to put here for EmployeeLogonID.)

    ' Build a SQL statement for the combo box
    Dim strSQL As String

    strSQL = "Select EmployeeID, myOtherfield,  From tblEmployees WHERE 
EmployeeID = " & lngEmpID

    ' Set the combo box recordsource
    MyCboName.RowSource = strSQL

    'Activate the combo box 
    Me.CboName.Requery

End Sub

If you need to show a value in the combo box other than the EmployeeID set 
the first Column width to 0"

--------------------------------
>2. Show All Records mode – I want the SQL record source to filter on that
> sales person’s records (EmployeeID)

Just add to your Recordsource Query: "Where EmployeeID = " & 
Me.mycboEmployeeID

Or:

Private Sub Form_Current()

    Dim strOriginalRecSource As String
    Dim strEmployeeRecSource As String

    ' Get the present RecordSource
    strOriginalRecSource = Me.RecordSource

    ' Add a criteria for the EmployeeID
    strEmployeeRecSource = strOriginalRecSource & " Where EmployeeID = " & 
Me.mycboEmployeeID

    ' Change to the New RecordSource
    Me.RecordSource = strEmployeeRecSource 

End Sub


' To Change the Recordsource back
Me.RecordSource = strOriginalRecSource 

Best Regards,
Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com


"Desilu via AccessMonster.com" wrote:
> I have one Sales Activity form that I use for data entry and for viewing all
> records by Sales Person (EmployeeID).  Without having to have multiple
> instances of this form, I want to accomplish the below; based on which
> EmployeeID (sales person) opens the form in either mode:
> 
> 1. For the data entry mode – I want the Combo Box’s Default Value to change,
> based on my user (EmployeeID);
> 2. Show All Records mode – I want the SQL record source to filter on that
> sales person’s records (EmployeeID)
> 
> I’ve got some code for passwords, so I hope I can utilize that part of it; I
> just do not know how to do the above two steps.
> 
> Thanks.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1
> 
> 
0
Utf
1/24/2008 9:13:12 PM
Hunter57 wrote:
>Hi Desilu,
>
>Here is some code that will change the RecordSource for the Combo box.
>
>Private Sub Private Sub Form_Load()
>
>    ' Create a variable to hold the Employee ID 
>    Dim lngEmpID As Long
>
>   lngEmpID = EmployeeLogonID(without more information I can't tell you what 
>to put here for EmployeeLogonID.)
>
>    ' Build a SQL statement for the combo box
>    Dim strSQL As String
>
>    strSQL = "Select EmployeeID, myOtherfield,  From tblEmployees WHERE 
>EmployeeID = " & lngEmpID
>
>    ' Set the combo box recordsource
>    MyCboName.RowSource = strSQL
>
>    'Activate the combo box 
>    Me.CboName.Requery
>
>End Sub
>
>If you need to show a value in the combo box other than the EmployeeID set 
>the first Column width to 0"
>
>--------------------------------
>>2. Show All Records mode – I want the SQL record source to filter on that
>> sales person’s records (EmployeeID)
>
>Just add to your Recordsource Query: "Where EmployeeID = " & 
>Me.mycboEmployeeID
>
>Or:
>
>Private Sub Form_Current()
>
>    Dim strOriginalRecSource As String
>    Dim strEmployeeRecSource As String
>
>    ' Get the present RecordSource
>    strOriginalRecSource = Me.RecordSource
>
>    ' Add a criteria for the EmployeeID
>    strEmployeeRecSource = strOriginalRecSource & " Where EmployeeID = " & 
>Me.mycboEmployeeID
>
>    ' Change to the New RecordSource
>    Me.RecordSource = strEmployeeRecSource 
>
>End Sub
>
>' To Change the Recordsource back
>Me.RecordSource = strOriginalRecSource 
>
>Best Regards,
>Patrick Wood
>www.advancingsoftware.com
>www.churchmanagesoftware.com
>
>> I have one Sales Activity form that I use for data entry and for viewing all
>> records by Sales Person (EmployeeID).  Without having to have multiple
>[quoted text clipped - 10 lines]
>> 
>> Thanks.

Thank you.  I'll give this a try!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200801/1

0
Desilu
1/24/2008 9:29:33 PM
Reply:

Similar Artilces:

windows update 04-15-10
I am running windows vista on a MAC computer with VMWare Fusion. I cannot get the service pack 1 or internet explorer 8 update to run. i get error code 490 and 800704090 Too late now! Support for Vista Gold (no Service Packs) ended on Tuesday, 13 April 2010! Computers running Vista Gold "will no longer receive software updates from Windows Update" until SP1 has been manually installed (which will require several Prerequisite Updates to be installed first). See http://windowsteamblog.com/blogs/windowsvista/archive/2010/04/13/end-of-support-for-windows-vista-with-no...

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

How do I make a 'box and whisker plot' graph in Microsoft Excel?
I am in Year 12 and am doing a stats assignment. The project states that i MUST insert a box and whisker plot for some data, but i don't know how to do this. Does anybody know? Hi, Here are some sites on the topic of stats charting not just box and whisker. http://peltiertech.com/Excel/Charts/statscharts.html http://www.processtrends.com/toc_statistical_charts.htm http://tushar-mehta.com/publish_train/data_analysis/16.htm http://people.stfx.ca/bliengme/ExcelTips.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Year 12 student who needs help" &...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Publisher 2000 changes default font
Having loaded Publisher 2000 onto a Win98 m/c I have discovered fonts used by Groupwise have changed. This implies that P2000 has changed the default font somewhere - any ideas please? How do I change the fonts in GroupWise http://support.novell.com/cgi-bin/search/searchtid.cgi?/10061270.htm -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "PS" <anonymous@discussions.microsoft.com> wrote in message news:1be4401c45220$f2b1b4a0$a601280a@phx.gbl... > Having loaded Publisher 2000 onto a Win98 m/c I have > discover...

Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...

Constructing Hyperlink from the Database Record fields
I am working on a Windows XP environment using MS Office 2007 including Access 2007. I want to open a document from Access 2007 which I can easily do with Hyperlink type field. However since all the necessary information is already in the Database Record I try to avoid creating additional field which would be a Hyperlink type on the Form unless it is absolutely necessary. Below is the code that I have to construct the FullFileName which consisted of ServerName, Division, Unit, RequirementDirectory, FolderName and the FileName itself. As you can see the Database records has al...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Is this a correct way of doing an optimistic update
Imagine two different users executing this code at the same time. I can not use the CRecordset Update Delete methods for this. // Check to see if version number has been incremented since this view read in // the client data CClientRecordSet rs(mConn); rs.m_strFilter = "ID = "; rs.m_strFilter += client->GetKey(); rs.m_strFilter +=" AND "; rs.m_strFilter += "Version = "; rs.m_strFilter += client->GetVersion(); // Assuming here, this is acting as a lock on tables referenced mConn->BeginTrans(); rs.Open(CRecordset::snapshot, NULL, CRecordset::re...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

host unreachable after recent Windows Update
Exchange 5.5 on Windows 2000 Server, fully patched and running real swell until Wednesday morning. (Tuesday evening: applied the GDI+ patch: MS04-028.) Didn't find out until later today: internal e-mail is fine, inbound Internet e-mail is fine, but nothing goes out over the IMC. Each outbound e-mail generates Event ID 3010 warning message in the App log: host unreachable. But NSLOOKUP on that very server successfully resolve those same domain names, and ping gets replies. The only other software on this server: GroupShield 5.0 all up-to-date, and VirusScan 8.0.0 patch 1 push...

Updating Related Entities Via Workflow
I'm a complete newbie to workflows, so your help is much appreciated. In general my question is, can a parent entity be updated via workflow rules when some event occurs on a related child entity, or does this need to happen via assembly or callout? Specifically... I have a situation where leads come into our system from various places. Because I cannot add leads to a queue, I use a workflow to create a phone call for each lead and add it to a queue. Now, is it possible (through workflow) that when a user accepts the phone call activity from the queue, that the parent lead own...

Excel formulation to automate values
Hi there, I have a little problem on arranging a small multi-worksheet excel here. Let me explain in a short way then give some details on it. I am trying to make an offers recordsheet which has two worksheets in it. One for products(and their base prices) and an offer sheet. I would like to use a bit dynamic data here. So when I type the name of the product excel would give me the price from other worksheet. in details; Prices worksheet is something like: A B 1 Product BasePrice 2 mouse 5 3 keyboard 8 4 ... And the ...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

Pay code update with Integration Manager
I am trying to do a Pay Code update for all employees using Integration Manager using the General Payroll Update. I am able to get the standard Pay Codes to update, but cannot seem to get the "Based On" Pay Codes to update. When I log into GP and view the Employee Maintenance and Go To Pay Codes, I see the standard update. However the Based On Pay Codes remain the old rates unless I select select the Pay Code and simply click Ok. Then the update automatically is brought in. How can I get these updates to take without having to visit each of the Based On Pay Codes and sele...

error message in Outlook after running windows and office updates
Hello, I am hoping someone can help me with this. When ever a few staff tries to send a new e-mail, a message "Do you want to save changes" come up and on the back of this message I see the spell checker box come up. The problem is that if you click on "no" on the error message, it comes up with another message something like "running out of memory". I just ran all the windows and office updates on all the machines in the company and so far two employees are having this problem. Is this something to do with the updates? Any suggestions? did you try ...

Default font in charts
Is there any way to change the default font in charts? ie, I'd like every axis and title to be 16pt Times New Roman right off the bat. Thanks. Alex "Alex" <Alex@discussions.microsoft.com> wrote in message news:6096B69F-DCF5-4BB8-8158-433A4A416053@microsoft.com... > Is there any way to change the default font in charts? ie, I'd like every > axis and title to be 16pt Times New Roman right off the bat. Thanks. Set up a chart the way you want it to look. Right-click in its chart area and choose Chart Type. Click the Custom Types tab and hit the "set as default...

Do money updates install when you're not admin?
I noticed when I first start using money it told me to log on as administrator so that the updates can take effect. Is this true of all money updates? When I see the message "Money update recieved" does that mean it has also been installed or do I have to log in as admin to complete the process? In microsoft.public.money, Brian H wrote: >I noticed when I first start using money it told me to log on as >administrator so that the updates can take effect. Is this true of all money >updates? When I see the message "Money update recieved" does that mean it >has a...

Continuous update unsuccessful--this is truly bogus
Any updated info on the online account updating issues in Money 2005/2006. Example. I have several credit cards listed and setup for online account access, downloaded transactions (advanced register), etc. Suddenly the account will get an error stating that "update unsuccessful" and then never updates after that. I have read where the advice was to delete the online account and reset it up. The problems I have with that are that when I do that, now all my transaction history for that account is lost and I am given only the balance. I have selected the max for account hist...

SQL-Dynamics V10 Users
I have some messy stuff going on with Dynamics and SQL users. I have users that are users in the databases, but not in Dynamics, and users in Dynamics that are not in SQL. Is there a script or anything to run to straighten this out? Right now it is manual process where I have to go user by user, check in dynamics and which companies they use, then go to SQL and check each database... Any suggestions? -- Doug Why can't you do this through the user interface? Did you resore a backup of Dynamics of something? As far as I know, the only way to create a GP login is to do it throu...

drop down boxes #14
Is it possible to change to fonts or fill colours of a cell containing a drop down box? Any help would be much appreciated Thanks If you made the drop down box using validation then you should be able to change the colors just like any other cell. If you used a combo box from the control toolbar, then you've got to right click and go to properties. Art "stumakker" wrote: > Is it possible to change to fonts or fill colours of a cell containing a drop > down box? > > Any help would be much appreciated > > Thanks Hi, Thanks for your reply....

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Form Formatting
I have a subform (datasheet view) in a form where I want to make one column a different color. I can go into Design view and change the color but it doesn't change in the form view. This form was made ages ago with an automatic format and now I can't get away from the automatic format. Help! "DSmith" <donna@DONTSPAMresxrn.com> wrote in message news:eQARsjI6KHA.4508@TK2MSFTNGP06.phx.gbl... >I have a subform (datasheet view) in a form where I want to make one column >a different color. I can go into Design view and change the color but it >doe...