Replies

Hans

As usual you are a real star, the ability to put this into a query was where I’d have been going next. I’d found the posts on the Access site by following the link to the ‘simple’ one that is on this forum.

Dave

Thanks, the information answers the one question I had left after Hans’ answer (I can run the ‘independant’ code from a form event).

This place is keeping me sane

Thanks again

Ian

OK, that’s pretty much what I thought was the situation, it doesn’t matter how a module is started, it will pause once a form is opened in dialogue mode. This did cause me a problem when I set the ‘setwarnings’ method and then tried to open the resulting form in design mode…. The screen appeared to have locked. I now know that what I thought was happening was the case.

Thanks

Ian

Hans

Thanks for the quick reply, the first reply in the requery post pointed me in the right direction, I set the form to dialogue mode and then put the ‘response’ line into my VBA module. This now works fine.

I’ve looked at the linked post you put up and it would work fine, I think, except that I’m adding other information as well, customer’s name, contact name, contact phone number, address etc. This information is needed later when the actual quote is produced from the system or Sales wont know who to send the thing to

Thanks again

Ian

Doh!!!

As I closed the first post I saw the requery post on this page, I think it may solve my problem. If not I’ll post again later. Wonder why it didn’t show up in my searches for not in list, notinlist?

Ian

Thanks for the tip, the form is opened in dialogue box form with no possibility of swapping to design mode. As I haven’t got any ‘real’ security on the forms I use this to stop the shop floor from altering things. The back end is password protected, which along with a few property settings on data input forms, stops people from accidentally deleting, or altering, information entered.

Now I’ve got it working I want to improve it…. Isn’t that just the way

Can I use a ‘class module’ to open the form from any point in the database? If so, how do I start the ‘class module’ when the database is launched?

As you may have noticed, I’m past the fear point on coding in Access, so now want to get some more practice in where it will be useful

Thanks for the helpful suggestions and moral support.

Yoohoo

Solved it. I run a macro to open the pop up form in the ‘on timer’ property of those forms that are most used by the Maintenance team, I’m going to try and be brave and write a ‘class module’ to cover all eventualities for this. Then, in the ‘on load’ property I have this code: –

Private Sub Form_Load()
If IsNull([New Job?] = True) Then
docmd.Beep
Else
If IsNull([Equipment Name] = False) Then
docmd.close acForm, “New Work Request”, acSaveYes
End If
End If

End Sub

I don’t think this is the best way of doing it, but it works for now.

Thanks for the shove towards using modules, I’m going to try and use a few more of them now

OK, back again, my head is getting sore and my desk just cracked under the repeated blows

I tried the code suggested, and didn’t have any luck, possibly as the forms aren’t linked, guess this should have been explained (by myself), and that I should have realised what the code was trying to do before the headache went critical….

So, I tried to do something along similar lines using the help in Access, this is what I’ve got at the minute: –

Private Sub Form_Load()
If IsNull([New Job?]) Then
docmd.close acForm, “New Work Request”, acSaveYes
Else

End If

End Sub

Now it compiles OK, but when used with the ‘on load’ property of the form, the form does not close, regardless of whether there are any records in the table or not!!!! let alone whether the yes/no property is yes or no??

If I move the docmd.close to the else section of the code, it always closes my form, so I’m guessing that the problem may be with something else in the database, so here is more detail.

The table I’m querying to put data into the form has an autonumber field to create an index, it is called ‘job number’. Now even if the table has no records in it, there is still a check box that is a null value due to the autonumber field, could it be this that is causing my problems?

I’m going to try and prevent the query from picking up on the autonumber field uinless it has a value in, but I’m not sure how to go about this, think I’ll try is not null first…..??

The best thing to come out of this so far is that I have finally got over my fear of using code in the database, this has got me to the point where I’ve actually ordered a book to teach myself Access VBA with, the dummies book (just right for my level of (in)competence) will be with me on Tuesday

Thanks again

Ian

Thanks, I’ll give that code a try, the ‘on timer’ property I had set would have been annoying as the form opened regardless of new jobs or not.

The database is a poor mans CMMS (computerised maintenance management system) and is used by our Maintenance Team, they cover a 24 hour period and the person seeing the job should act on it, so no need for a daily / weekly option. I have other forms for the Manager to view what has been done so the weekly option is covered there. The Maintenance Team only click the Yes/ No box when they have acted on and completed the activity requested.

I’ll let you know how I get on with the code.

Thanks again for thr help and suggestions.

Thanks. I’ve got the forms and query working fine now. All I need to do now is get the ‘on timer’ property to only open the form if there are records in the yes/no field that are still set to no. I thought I had an idea about this, and it didn’t work out. At the moment tjhe form will open every x minutes depending on the timer setting, can some one please suggest either some SQL, or code that I can put into the ‘where condition’ property in the openform command I’ve used in the macro that runs from the timer event?

Thanks again.

This is the sort of thing I was thinking of, it’s nice to know that I’m not barking up the wrong tree.

Thanks

Ah well, I like setting people a challenge, and if IT can’t find a copy, we’ll have to buy the licenses, probably Microsoft’s intention all along…..

So, all I have to do is ‘show’ the developer’s kit where my database is and start the process? Sounds easy if that’s all. I’ll get onto our IT people to order the developer’s kit this afternoon.

Thanks for the reply.

Yippee!!!!!

Solved the problem. While trying to get the ‘stuff’ into a small enough database to upload I found the error, one of the tables my queries are based on is an Excel file (linked). Apparently linked files do not have to obey the same naming conventions as tables, they have a column name, but if you try to import the table (I was doing this to upload the database) Access will not recognise the table names for Access tables. I changed the names of the columns in the Excel spreadsheet and my queries now work.

I posted this (embarrassing moment) in case it helps anyone else avoid this pitfall.

Thanks to those who offered help.

The database has been running for 2 years now, and is around 20 meg in size, even with only the relevant forms, queries and a little sample data I have almost a meg of ‘stuff’. It didn’t seem to zip very well, still around 800kb. As I’m on a 56K connection uploading this isn’t too high on my list of things to wait for.

Thanks for the offer of your assistance though, I don’t want to sound as though I don’t appreciate the help offered, I do.

If I can find time to re-create the working parts in question separate from the user interface I’ll zip this and upload it.

I’ve managed to get a result out of my current queries by changing them to make table queries and then using yet another query to do the math. I was wondering if anyone had spotted what I’ve done wrong in the previous queries that stops the data in them from being manipulated by a query, but allows it to be manipulated once in table format.

I’ve looked at the properties options for the query, but can’t spot any obvious errors.