jalbright1957
Last Activity: Jan 31 '19, 01:52 AM
Joined: Mar 12 '16
Location:
-
I actually started it this way but wasn't 100% sure of how to convert the year that was input into the sql string. I did see several articles about using variables within the sql code but could not get the global variable to pass the value and avoid sending up a second prompt. Interestingly enough that by referring to the form worked first time. Thanks for the article tip, is very helpful. -
Phil, thanks for your reply. I tried that but got the same result of Access popping up with another parameter prompt. I did discover that by changing the Where clause to reflect the form instead of the variable, it worked as expected. I actually did start out with a combo box in the header but the form was too crowded with just that little extra....Code:WHERE Year([BidDate])=Forms![frmBidYearInput].[txtBidYear] ORDER BY BidDate ASC;"
Leave a comment:
-
SQL search using Year() function
I have a popup form opened as acDialog which prompts the user to enter just the year with which to display all relevant records in a continuous form. The variable BidYear passes the correct value back to the main form where the SQL code is executed but Access throws up another input box to enter a year. If I remove the variable BidYear and instead type in a year such as 2016 in its place, all records whose date field contain a date in 2016 are... -
Add child record using Not in list event
Need some help with using the Not in list event. Several tables with one to many relationships and a M2M. Of these tables I have a customer table with a FK pointing to a City table. The City table has a FK pointing to a State table. All works well when adding new customer records provided the state with it's associated city child records have already been keyed into the tables - enter a new customer, select the city from a combobox and it auto-populates... -
Hi Phil,
So I understand you to say add a FK to the company table which points to the PK of the city table. The city table would have a FK pointing to the PK of the state table. Never mind the bidder that is listed in your diagram, the company is the bidder.
I made an attempt and got it working or at least at this point. I manually entered records into the city & state tables. I just haven't tested out my routine for the...Leave a comment:
-
Hi Phil,
Been putzing with this for the past several days when I am able to afford the time. I broke off the master table "State" and made it a child table of the City table. That does populate the State field when the City is selected but since the Company table is a child table of the City table also, I can't seem to find a way to pre-populate the City field when the end user selects an existing Company as a bidder for a job...Leave a comment:
-
Company table has a FK pointing at the city & state tables, I added them into the subform as a combo box. Selectin the city and state from the combo box removes the error dealing with the city & state records but now am getting a duplicate warning when saving. I believe my logic in all of this is wrong in how I have the forms designed.Leave a comment:
-
I did not setup the state table that way. I never thought about that but may be the better way to go as far as the city & state work.
In the subform, I have a combo box to select the company but do not have a combo box to select the city & state. I was trying to populate the city and state records automatically based upon the company name. I can appreciate that in order to use the child record, the parent has to be present....Leave a comment:
-
Form using a many to many relationship
Little stymied by behavior for a form I have. To begin, the tables being used are tblCompany, tblJobs, tblCompanyJobs, tblCity & tblState. tblCompany & tblJobs are many to many with junction table tblCompanyJobs. tblCity & tblState are master tables for tblCompany & tblJobs.
The main form is used to create Job records which includes the city & state location. City & state are recorded elsewhere and are chosen... -
Thanks Jforbes... That took care of the problem. Appreciate the solution.Leave a comment:
-
I didn't get an error by commenting out the error handling. On the first entry into the event, debug.print shows 1 out of 1 records instead of showing 1 out of 10 records (currently there are 10 records in the table.) As long as the breakpoint at the entry of the event is active, the controls work as planned. Remove the breakpoint and it doesn't read the table.
In case this sheds a bit of light into what I'm doing. The tables being...Leave a comment:
-
A bit of an update: If I enable Navigation Buttons in the form properties, my custom navigation buttons work as designed. Disabke Navigation Buttons in the form properties and my custom buttons fail when launching the form. Obviously I am missing something important here.Leave a comment:
-
I can't explain what may be causing this. I thought it might be due to it taking too long to read records and report back but the table doesn't have very many records.Leave a comment:
-
Custom Navigation Controls Odd Behavior
On the form in question i have records from 5 related tables. In the footer of the form I have navigation controls (default navigation controls are turned off). When the form is launched, the navigation controls always appear disabled (grayed out). If I debug the code and put a break point where it enters the if conditions for the On Current event of the form and step through it, the navigation controls work fine. I do have a combobox on the... -
I neglected to check what the data type arguments are supposed to be when using openargs and didn't realize it required a text value. Since I'm using the PK (numeric), obviously this wouldn't work. I would rather not go through data conversions so on to a different method. I'm still green with Access and have to look up most methods. Have a better method you can recommend?Leave a comment:
-
Runtime error 2498 - Wrong data type
I have a navigation form with (3) subforms within (1) tab. The customer table in this particular instance is the parent to the customer contacts table. Customer form is a single form, contacts form is a continuous form. Customer table has the PK CompanyID, customer contacts table has the PK ContactID and the FK CustomerID.
In the footer of the customer contact form there is a button to enter a new contact and selecting it opens... -
Thanks for your response Phil,
Yes, I have been trying statements that are similar and by all rights, should have worked. Well, they would have worked if I had the name of the subform correct. The name of the entire navigation form is frmContractorNa vigation but what I missed was the name of the subform which I discovered is the entire area that encompasses the 3 subforms. That particular area of the Navigation form is somewhat difficult...Leave a comment:
-
Requery a subform that is in a navigation form
This topic has quite a few posts with various remedies that I have read but haven't been able to locate one that works with what I am doing. I use Access 2016. I have a navigation form. In the first tab of the navigation form, there are (3) subforms which contain data from five separate tables, all of which share a 1 to many relationship among themselves.
The subform that I cannot get to update displays customer contacts in a continuous... -
Very odd thing this is. Yesterday this would not work on the client machine in question but right after I posted the previous reply I went and checked again and now it opens fine. Go figure... At any rate, thanks for help...Leave a comment:
-
Correct me if I'm wrong but I had the database open, went to the Access settings Options>Current Database>Displa y Form and entered the name of the form to load then saved the database. Correct? if correct, Windows 10 issue maybe? or the 2013 runtime fails when run under Windows 10 with Office365 2016?Leave a comment:
No activity results to display
Leave a comment: