Replies

(Edited by WendellB on 01-May-03 08:01. activate link – see Help 19)

Help!… I can’t stop looking for this now… (When I get a question in my head… look out!!… )

I wasn’t sure whether that file would help you or not… so I found this code at: http://www.erlandsendata.no/english/vba/pr…angeprinter.php%5B/url%5D

‘Change the default printer
‘This example macro shows how to print a selected document to another printer then the default printer.
‘This is done by changing the property Application.ActivePrinter :

Sub PrintToAnotherPrinter()
Dim STDprinter As String
STDprinter = Application.ActivePrinter
Application.ActivePrinter = “microsoft fax on fax:”
‘ change printer
ActiveSheet.PrintOut
‘ prints the active sheet
Application.ActivePrinter = STDprinter
‘ change back to standard printer
End Sub

Let me know how it goes…

(Edited by HansV to make URL live – see Help 19)

Good Morning David…

I have never had to do this, but I was sure that I’d seen how to somewhere… Doing a search on Google for “vba changing printer” came up with numerous hits… I found the attached file at : http://www.mcwtech.com/downloads.htm%5B/url%5D

It’s an mdb done in Access 97 and I’m sure you’ll have to adapt it… but hopefully it’ll help…

Have a great day!

Thanks Aladin!

I tried your solution and it worked great except one thing… I failed to mention that the non-zero dollar amounts can be negative… There was one row with a negative value and that was the only one that returned an incorrect RegionID… I think the MAX stuff is where the problem is, but I’m not sure how I’d fix it… I’ll try and figure it out, for future reference…

Have a great day!

Thanks Hans! You’re the best!

I tweaked it a bit for other tests I had to do but it works perfectly… AND I learned a couple more Excel functions in the process… That’s always a good thing… Thanks again! Have a great day!

Hmmm…. Well my first thought is this….

I’m assuming the Inmate and Meds Tables are “Inner” joined in the query in question… That would show only the inmates showing in the Meds and Inmate tables… I would change the join to a Left Join (using the Inmate table as the primary table) … That would show ALL Inmates… whether they matched the Meds table or not… Then create a new field in the query, to put the None in…

For example:
fldMedsOrdered = IIf(IsNull(fldMeds), “None”, fldMeds)

Does that help at all? (I hope I didn’t completely misunderstand the issue… )

Thanks Charlotte… I should have known better… lol…

Do you have a button for saving the new record?? … If not, my suggestion would be to put a quick validation routine in the Form Unload event…
Maybe something like:

Private Sub Form_Unload(Cancel As Integer)
Dim ctl As Control
Dim blnMissingData As Boolean

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If IsNull(ctl) Or ctl = “” Then
blnMissingData = True
End If
End If
Next ctl

If blnMissingData Then
MsgBox “Data missing, please ensure all required fields are complete… Record will not be saved as is… ”
‘set the focus back to the first text box
Cancel = True
blnMissingData = False
End If

End Sub

Now this routine will check all text boxes for data… Obviously it’ll need adapting if you only have certain controls that are mandatory… but hopefully this will give you an idea… Have a great day!

HTH

Hi there…

This MAY be a waste of time since I only have Access 97 SR2 at work, but I’ll give you this answer and hope it’s close to how you do it in that version.

k… I’ve attached an example for you to see… Here’s how I do it…

1) For the One side of your relationship… from table design mode… you just select both fields (rows in the fld definitions) that will make up the key, right click and select primary key… (Make sure the indexing is as you want it…) … You should see the key next to both field names… Save the table design…
2) Then in the Many side… table design mode… Ensure that the two corresponding have indexing “Yes (Duplicated Ok)” … Save the table design…
3) Then add both tables… in order (tblOne first, then tblMany) to the relationships window… Drag the first field of the primary key in the One table over to the first foreign key in the Many table… A window should pop up where you define the relationships…. Fill in the second field of the primary key and the corresponding one in the Many table… Check the “Enforce Referential Integrity” option… Click OK… As soon as the window disappears you should see the symbols for One-To-Many relationships in both places…

All done…

Hope this makes sense… and HOPE it works the same in 2002… Let me know how it goes… I don’t get the opportunity to answer questions on here often…

Hi Andrew…

Any particular reason why you don’t just update the old records with the new information?… (rather than deleting them and adding new ones)

Okay… This may be a realllllllllyyy silly response… but whenever that kind of thing happens for no apparent reason ,I start thinking possible corruption…
Have you tried compacting/repairing or maybe importing everything into a new blank mdb?? …Sometimes that works for me… It’s just a thought…

HTH

Oops… Almost forgot… The “Expected 1” part of the message means just that… There is 1 field it is looking for a value for…

This message means that you are neglecting to give Access a value that it needs for execution… I have copied the help information from Access 97 so that you can read it for yourself…

“Too few parameters. Expected . (Error 3061)

This parameter query requires the specified number of parameters, which you didn’t supply. Provide the expected number of parameters, and run the query again.
In some cases, this message is generated when unknown field names or expressions are interpreted as query parameters. Be sure to enclose field names containing spaces or punctuation with square brackets [ ]. ”

Check the fields and expressions in your query design and see if there is a field name you mistyped (or that doesn’t exist anymore), etc….

Hope this helps… but if not… try posting the SQL view and maybe that would be easier to help you with…
Happy Friday!

Well this is a new one for me… I know I don’t have half the experience of many people here… but I’ve never seen a query that updated more than one table at once…. Unless the job is done with relationships among the tables??

Sounds to me like you need to create some relationships with referential integrity enforced….

Also… the delete query you were talking about…. does that mean you just want the customer number to be deleted and the field left blank, but the records to remain?…. or do you want any and all records in all three tables for that customer number deleted simultaneously?

Stewart… You’re a lifesaver!!

I thought Hans’ idea about bringing the file into Excel was working… but I ran into a few problems with that… 1) It would only allow me to import 16,384 records from the .xls file…. (the table has over 20,000) … and 2) For some reason beyond me, Excel was formatting a text field (even though I had created an import spec… Strange but true)… and that was resulting in a lot of queries not working… joins, etc…

Anyway…. I tried fixing the file with your code and it worked perfectly…. Thank you so much for your help….

Oops… Mark beat me to it….
Have a great day!