Problem with populating form

[WLC]

I have a form that I want to auto populate with some data based on a query.

First off a form pull 4 pieces of data from a master table and passes them to a query which then auto populates another form that shows a lot more of the information from the master table in a more user friendly way.

The data is in a certain sort order and I want to be able to "click a button" and the next set of data (which is different based on a different set of criteria) will show. I have a different query from the one previously mentioned that runs but I can't get it to increment the sort order or populate the form.

Any thoughts.

 

[CJ_London]

Not without more information like your sql code and what 'increment the sort order' means

 

[WLC]

I have provided additional details in the attached file. Please let me know what else is needed.

 

[CJ_London]

So are you saying, when you click the next button you want to display the next set of data?

If so, your form recordsource should include all the records you want to be able to go to and the code behind the button click event would be

On Error Resume Next
DoCmd.GoToRecord , "", acNext

Note: There is a button wizard which will complete this code for you if you follow the prompts

 

[WLC]

Yes I am saying, when I click the button I want to display the next set of data. Which with the code you gave me takes me to the next record, record #2. Thats not what I want. I want a completely new set of data based on the field "Checkout Order". It's going to take a requery of the data to repopulate the form and I'm not sure how to do that.

Here's what I think needs to happen, I just don't know how to accomplish it:

1. Form is currently popluated with data.
2. Hit Next button.
3. Macro runs that will close current form, somehow increment my "checkout order" field, rerun my query, and open my form back up
4. Form is repopulated with new, updated data.

More information about my Checkout Order field: it is a numeric field, groups of records that go together have the same number (that's not the only thing that make them go together).

I'm not so worried about making buttons work. I'm more worried about how to get to my next set of data.

See attachement as well.

 

[CJ_London]

I think you need to provide me with much more information - can you upload your db?

 

[WLC]

Here is a stripped down version of the database. The form to get everthing started is frmEnterParameters.

I do so much appreciate the help!

 

[WLC]

CJ_London - For some reason I am unable to see your reply in this thread. Do you think you can repost?

 

[CJ_London]

OK, just need to confirm what you want.

on the frmEnterParameters we have this

If you have selected the top one (AECI202-Sedal W-Nort AEC L9) you want the next button to take you to AECI203-Clntn161-Clinton etc?

Assuming this is the case there are some solutions, but don't want to spend time explaining until I'm sure I understand what you want to do.

Another question - you have a next button - don't uou also need a previous button? answer will affect the final solution

 

[WLC]

That is correct. If I hit the Next button, it should take me to the AECI203-Clntn161-Clinton, and so forth. A Back button would be nice but I didn't want to complicate matters too much.

 

[CJ_London]

Another question - do you have a problem if you made frm01ATFTieLineCheckout a subform of frmEnterParameters - makes for an easier solution

 

[WLC]

Not at all. Whatever is going to get me to my goal.

 

[CJ_London]

OK this is now working see the copy form - user has a choice of selecting an option from the drop down or use previous next buttons

I've dropped your query and included a simpler variation in the recordsource for the form and corrected some errors you had in your code to do with your cascading combo's

 

[WLC]

Thank you. Let me take a look at this and I'll get back to you. It may be a day or two. I really appreciate this.

 

[WLC]

This works perfectly. Thank you. Is it ok if I delete the posts where my databases are uploaded?

 

[CJ_London]

no problem from my perspective

 

[WLC]

CJ_London, I have a few more questions.

1. On the frm01ATFTieLineCheckout form, I have conditional formatting set up for the "DeltaIn" field. However it's not what I want. I need it to be a percentage (i.e. if the value is > 5% the field hi-lites in yellow).

2. Lets say the form is populated with values for a Tie Line. I want to Update one of the Hourly Adj MWH and have it write back to my Master table. Is that possible?

3. How do I lock a field where it can't be updated?

Thank you for your help!

 

[CJ_London]

1. On the frm01ATFTieLineCheckout form, I have conditional formatting set up for the "DeltaIn" field. However it's not what I want. I need it to be a percentage (i.e. if the value is > 5% the field hi-lites in yellow).

You need to use the format property of the control to format as percent, modify decimal places as required - in your recordsource you'll need to divide the current value by 100 to get 45% - if this is supposed to be 4.5% or .45% you'll need to divide further. Alternatively if you want to leave the value as it is, modify your conditional formatting to >0.5 or >5 depending on what you are displaying

2. Lets say the form is populated with values for a Tie Line. I want to Update one of the Hourly Adj MWH and have it write back to my Master table. Is that possible?

- yes, just change the value, you can do it at the moment

3. How do I lock a field where it can't be updated?

you set it's locked property to true. If this is conditional then you can't set the locked property , you can only set the enabled property (to false)

 

[WLC]

Now I want to combine frmEnterParameters2 with frm02ComparePriMWHtoSecMWH. There's a query that populates the form but I like the way you did it on the last one. I tried to make my record source the same as what my query was but it just didn't work. This is what I tried to make my record source:

SELECT tblFinalMaster.*, FROM tblFinalMaster
WHERE (((([tblFinalMaster].[Import])<>[tblFinalMaster].[HourlyAdjMWHin]) Or (([tblFinalMaster].[Export])<>[tblFinalMaster].[HourlyAdjMWHout]))

What did I do wrong? In my database this is qry50ComparePriMWHtoSecMWH. When I enter ed the above for my record source, it just changed it to SELECT tblFinalMaster.*, FROM tblFinalMaster and thene it won't populate my form.

Thoughts?

 

[CJ_London]

you ought to try to use the query builder until you understand SQL syntax better

SELECT tblFinalMaster.*, FROM tblFinalMaster
WHERE (((([tblFinalMaster].[Import])<>[tblFinalMaster].[HourlyAdjMWHin]) Or (([tblFinalMaster].[Export])<>[tblFinalMaster].[HourlyAdjMWHout]))

You have an extra comma before the FROM and you are only referring to one table - tblFinalMaster, so I'm not clear what you are trying to do