Replies

Sigh, no it isn’t. I even tried taking the SQL statement out of the rowsource property to see if that would make a difference and to see if only using the command button to fill the listbox would help, however I get the same parameter question.

Leesha

Are you sick of me yet? I got error messages looking for parameters for frmtblTimesheet!Startdate and the same for enddate.

RE the listbox name……………ya I know. I’m updating this database for a friend and have had so many problems with it I didn’t want to change too much. I normally name everything. Fortunately there are only two listboxes on the form and I’ve spent so much time with them that I have them memorized!

Leesha

This is the code I put behind the command button. I don’t get error messages, however the listbox becomes completely blank????

Me.List48.RowSource = _
“SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.ClientID, tblTimeSheet.AccountId, tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], tblTimeSheet.EmployeeID, tblTimeSheet.Paid” & _
“FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID” & _
“WHERE tblTimeSheet.Date Between Forms!frmtblTimesheet!Startdate And Forms!frmtblTimesheet!Enddate” & _
“ORDER BY tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name]”

Leesha

Thanks Hans! Will give it a shot.

I just posted a new thread re the issue I’m having with numbers replacing the Account name. There is no ryhme nor reason to how its happening!

Thanks,
Leesha

OK, I told you’d I’d be back. This database is quite large and the owner would like to limit the list box in the AR sheet to only show records within a specified date range that they enter. If I put non-bound “To” and “From” nonbound input boxes on the form, how would you suggest entering the code? The present rowsource code for the list box is as follows:

SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.ClientID, tblTimeSheet.AccountId, tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], tblTimeSheet.EmployeeID, tblTimeSheet.Paid
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
ORDER BY tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name];

I cut and pasted this directly from the database SQL view so I know this piece runs appropriately. Now I just need to code for the dates.

Thanks,
Leesha

Finally!! I had tried your suggestion earlier but kept getting limited versions of the records with not all listed. When I’d take out the actual name links I’d get the entire databse. I made a few changes to properties (I’ve made so many that I don’t even remember what I’ve done at this point, and now its working!! Thanks so much for you patience. I have a feeling this won’t be the last post I make on this database!

Leesha

Here’s a shot of how tblTimesheets looks.

Leesha

God I appreciate you quick replies. The rowsource data comes from tblTimesheets which no longer has ClientID, AccountID, and EmployeeID now in it as numeric values which lookback to the names in the tblClient, tblEmployee, and tblAccount so that the names show in the combo boxes on the form. I removed the actual Client Name, Employee Name, and Account Name from tblTimesheet after the referential links were set as per your instructions yesterday (thanks for the detail). Should I have kept these?

Thanks,
Leesha

Next problem……………..I’m slowly, very slowly working through the form, query and report changes that are a result of the tblClient, tblAccount and tblEmployee ID changes I made for referential integrity. I’m stuck on list that when clicked on it would go to the line item for that row in the database. Now when the list opens, the ID numbers vs the actual names show. I’ve tried adding the associated name fields from the corresponding tables and although that helps, for some reason it limits the amount of data that shows up. There are no parameters placed on the query such as date range etc. When I run the actual query in design view it shows the name. It’s only in the list box view that only the numbers show. Is there a way for me to change this???

Thank you,
Alicia

Nope, the update query went fine. Not one error message and the I went back and checked the data and it was valid. To make matters worse, it only changes the the data in one line item at a time and it varies. It doesn’t go back and replace the ID number for every cell in the database. Sigh, gonna be a long day.

Thanks!
Leesha

Morning!

The Account Name Field in tble Account has actually been modified to contai ID’s instead of names. I went back and rechecked all queries and found 2 that didn’t actually have links between the tables and thought that might of been it, and yet it is still happening. I’m not sure where to begin to look. I’m about ready to trash the whole database and rework it but the owner has sooooooooooooo much data in it!

Thank you
Leesha

Hi Hans,

Well, I followed your steps for all three tables (Client, Account, Employee) and the referential integrity is set and all the forms etc. changed and running fine. My ONLY issue is that in all three tables (client, account and employee)the name field changes to the ID number on a whim, only one at a time in each database. IE, if I’m running account related reports, the account name will change to the ID number that is associated with it. Any idea where that is coming from?

Leesha

Well, I’m a glutton for punishment! That’s what I get for helping a friend out!!! I appreciate the input and of course the perfectionist in me will make me fix it so I’ll be getting to work on it tonight.

Thanks!
Leesha

I’ve attached the preview shot you requested.

Thanks,
Alicia

Morning Hans!

I already did the steps you recommended multiple times. I’ve tried making the primary key the name vs the ID number and that doesn’t work either. I’ve never had this happen before so its stumped me to no end which was I thought maybe it had to do with the three name tables that the Timesheet table references, each having fields called ID. It doesn’t help that this is an already established database with tons of data in it (which of course I have an original saved of), vs one I’m developing from scratch. To make matters worse I’ve already set up multiple reports using your help from the other day to my post referencing using list boxes to open reports, so pulling the ID catagory will reduce me insanity!!! Ugh. Now what??

Leesha