Change list in Lookup Field in Form without changing previous inputs

[Aenon]

Greetings people of Access World Forums!

I have a small issue with a time sheet database. I started making a database some time ago, but then i came over this awesome premade database which you can download directly from access. So i made some tweaks to make it fit for my purposes and stuff like that.

Anyway, my problem is only one lookup field i have in my Form frmWorkHoursList. In frmWorkHoursList.Project i have made a lookup field that will list all the projects in qryProjectsActive where the column Active=True. I want my lookup to only list choices where a project is currently active. However, whenever i make a project inactive (ended projects), the project code also disappears from all the cells where this project is already listed. I would like the cells to still display the unactive projects, but the lookup should only display the active ones.

Anyone that can help me fix this tiny issue would be of great help! I have attached a database with dummy names for you to check out to see if you can help me

Thanks in advance!

 

[NickHa]

In your control 'Work Code' whose Control Source is 'Project_ID', add a WHERE clause to the Row Source to restrict the lookup to only active reords. The Row Source would look like this:

SELECT ID, Project_Code
FROM qryProjectsActive
WHERE Active=True;

All other queries on the table would not have the WHERE clause and will thus return all records.

 

[Aenon]

Thanks NickHa. However i already tried that too This will give the same result. Once I save the new project list after making a project inactive, the WHERE clause will also remove the project code from all cells it has already been listed in the tblWorkHours. Do i need to make other changes to make it work?

 

[John Big Booty]

You could use a filtered Row Source for new records and an unfiltered row source for existing records.

 

[Aenon]

Right... uhm... I'm really not that proficient in Access that I have any idea how to do that Any chance you could specify? Or show me in the database i uploaded?

 

[Aenon]

Im trying again. Bump

 

[John Big Booty]

Dude keep your pants on, ten minutes aint a lot of time to respond especially if I'm having dinner.

You could use something like the following in the form's On Current event;

Dim intNewRec as Integer

Dim strSQLNewRec as String
Dim strSQLOldRec as String

intNewRec = Me.NewRecord

strSQLOldRec = "SELECT [YourTableName].[YourRecID], [YourTableName].[Record], [YourTableName].[Active] FROM [YourTableName] ORDER BY [Record];"

strSQLNewRec = "SELECT [YourTableName].[RecID], [YourTableName].[Record], [YourTableName].[Active] FROM [YourTableName] WHERE ((([YourTableName].[Active])= True)) ORDER BY [YourTableName].[Record];"

If intNewRec = True Then
     Me.YourComboName.RowSource = strSQLNewRec 
Else
     Me.YourComboName.RowSource = strSQLOldRec 
End If

 

[Aenon]

Thanks John! Although I have waited since friday, so it will be 72 hours and 10 minutes Not that it really mattered, i wasnt in a rush. I just want to make this database work eventually.

I see that you are speaking on a level waaaay above mine here though. I dont really understand how to implement this code. I go into the form where i want to use this code, and find the Form property sheet. From there i go to the event tab, and i open a "Code Builder" on the "On Current" line.

I tried to replace some of your input with what i thought would fit, but i dont know what you mean by "Me" in your records Me.YourComboName.RowSource etc. I assume that "YourComboName" is supposed to be the name of the ComboBox that is used to list my records though. And RowSource i suppose Project_Code. This is, however, pure guess work on my part. I have never encountered this stuff in creating a database before as I am a total newbie I have only created one asset tracing database before, and my access experience is pretty much limited to the work that came with that.

My code where i simply replaces your wording with my tables and records looks like this:

Private Sub Form_Current()
 
Dim intNewRec As Integer
 
Dim strSQLNewRec As String
Dim strSQLOldRec As String
 
intNewRec = Me.NewRecord
 
strSQLOldRec = "SELECT [tblProjects].[ID], [tblProjects].[Project_Code], [tblProjects].[Active] FROM [tblProjets] ORDER BY [Project_Code];"
 
strSQLNewRec = "SELECT [tblProjects].[ID], [tblProjects].[Project_Code], [tblProjects].[Active] FROM [tblProjects] WHERE ((([tblProjects].[Active])= True)) ORDER BY [tblProjects].[Project_Code];"
 
If intNewRec = True Then
     Me.YourComboName.RowSource = strSQLNewRec
Else
     Me.YourComboName.RowSource = strSQLOldRec
End If
End Sub

I feel like im asking a lot here, but could you explain further how I need to move forward?

I appreciate the way you are trying to teach me this though! Learn by doing usually makes things stick..!

 

[John Big Booty]

Whoops Sorry my bad, I just saw the time

Any way; Me. is the reference to the active form. In the code the only thing you should need to adjust are the portions which I have headed up with Your...

 

[Aenon]

I have tried now to change the code in the places you specify. However, all records dissapear, both the ones that have already been entered, and the new ones i want to enter. The list in the Combo box is empty. The code at the end of the post is exactly as i have written it in my code builder.

I assume the problem is because of the ME.NewRecord. As far as I understand this code this line tries to assign a value to intNewRec that has not yet been defined. I have never programmed in Visual Basic before, but is NewRecord a standard value or integer that is defined in the lauguage itself? Otherwise my previous experience in other programming language says that this command would not yield anything. Also Me.Work_Code.RowSource is weird to me. I guess RowSource is the list that should pop up when i open the Combo Box in the "Form View" of the form? Am I still missing something?

Private Sub Form_Current()

Dim intNewRec As Integer
 
Dim strSQLNewRec As String
Dim strSQLOldRec As String
 
intNewRec = Me.NewRecord
 
strSQLOldRec = "SELECT [tblProjects].[ID], [tblProjects].[Project_Code], [tblProjects].[Active] FROM [tblProjets] ORDER BY [Project_Code];"
 
strSQLNewRec = "SELECT [tblProjects].[ID], [tblProjects].[Project_Code], [tblProjects].[Active] FROM [tblProjects] WHERE ((([tblProjects].[Active])= True)) ORDER BY [tblProjects].[Project_Code];"
 
If intNewRec = True Then
     Me.Work_Code.RowSource = strSQLNewRec
Else
     Me.Work_Code.RowSource = strSQLOldRec
End If
End Sub

 

[Simon_MT]

You have two functionalities - one to list Tiimesheet Activity and the other to enter the TimeSheet Acitvity. You don't need a query in the combibox for Project to list the Projects for Timesheet enquiries if you include the Project in underlying query. However because you not using the Project's Primary Key rather the Description, this description will be lost on any Non-Active Projects if Active=True.

The problem with Continous Forms, correct me if I wrong, it would appear that the Row Source gets changed for each and every record rather than just the New Record.

I have had a similar problem with images on a Continious Form and not being able to dynamically set the image for each record. It wasn't until Access 2007 that this issue was resolved.

One solution maybe to have an Enquiry Form and an Entry Form.

Simon

 

[Aenon]

Im not really sure I understand exactly what you are talking about at the start of the post there Simon. But i do like your suggestion about just using two forms. I will have to look into that if i cannot make John's script work.

Thanks

 

[John Big Booty]

...

The problem with Continous Forms, correct me if I wrong, it would appear that the Row Source gets changed for each and every record rather than just the New Record.

...

Correct, with continuous forms the record that holds focus determines what happens on all other records being displayed on the form.

 

[Galaxiom]

Individual row sources could be simulated for combos on Continuous Forms by poping up a small stripped down form containing a combo right on top of the combo in the main form.

It uses the Click event of the combo, determines the appropriate rowsource, calculates the position, moves and sizes the popup to the right place. It would not be a simple thing to achieve.

 

[Aenon]

Thanks for your help everyone!

In the end I just went with Simons solution of using two forms. I just could not make the Visual Basic script to work. I dont think I understand how that works well enough to pull it of as of right now.