Microsoft Access Forum

Welcome to the Microsoft Access Forum. Access is by far our most popular topic here at Computer Learning Zone. This forum is for everyone to read but only students can post here. If you'd like to become a student, all you have to do is enroll in one of my courses, which you can do for as little as $1. After you create a logon, you will have full access to the Forums.

Subscribe to this forum and receive an email update whenever new posts are added. You will also be notified by email if any conversations you are involved in receieve new comments. Yes, even non-students can subscribe to update notifications.

NEW: Access Developer Students, make sure you subscribe to the Developer Student Forum.

Click Here to Post a comment and start a new discussion

Please read the FORUM RULES before posting.

Birthdate in Function Upload Images Link
Ina van Rooyen
15 hours ago

I want to use the birthdate as the date. I tried it like this and inside quotes "birthdate" but it doesn't like it.
Public Sub UpdateAge()
CurrentAge = Null Read More...

Richard Rost
15 hours ago

What do you mean by "it doesn't like it?" Are you getting an error message? If so, what is it? Which line? Read this please.

What are you doing with HowLong? It's a function that returns a value. So you'd need to say something like:

AgeFieldOnMyForm = HowLong(BirthDate, True, "ymd")

See Access Developer 2 for the difference between Subs and Functions.

Richard Rost
15 hours ago

Also, the way you have that written, if there is no BirthDate in the table for that dog, it will return a NULL and throw an error. Wrap DLookups like that in Nz.

BirthDate = Nz(DLookup("BirthDate", "DogT", "DogID=" & DogID),0)

If BirthDate = 0 Then Exit Sub

Show 2 More Replies...

Sort List Box from Combo Upload Images Link
Brent Rinehart
19 hours ago

I have two combo boxes that need to filter a list box (BidList), Filter by Office and Year the year is being filtered from the BidDate from the PropT, which stores the full date. YearCombo is populated from YearT and is unbound, Column 0 is YearID, Column 1 is YearSort and Column 2 is YearNumber. OfficeCombo is populated from OfficeT and is bound to OfficeName, Column 0 is OfficeID and Column 1 is OfficeName. Read More...

Kevin Robertson
19 hours ago

Hi Brent,

You are referencing your ComboBoxes inside the quotes.
Try this:

"WHERE YEAR(BidDate)=" & YearCombo.column(3) & " " & _
"AND OfficeName =" & OfficeNameCombo.column(2) & " " & _

Richard Rost
18 hours ago

Don't forget quotes around OfficeName

"AND OfficeName = """ & OfficeNameCombo.column(2) & """ " & _

Show 7 More Replies...

Brent Rinehart
12 hours ago

Thank You guys for helping me out I really appreciate it. as soon as I got home I attempted what you guys said and its still giving me a compile error. These quotes are the death of me. Bellow is what I tried.

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _
    "AND OfficeName= """ & OfficeNameCombo.Column(2)& """ "  & _
    "ORDER BY " & Forms!PropListF!SortBy

Brent Rinehart
12 hours ago

Thank You guys for helping me out I really appreciate it. as soon as I got home I attempted what you guys said and its still giving me a compile error. These quotes are the death of me. Bellow is what I tried.
Looks like I missed something I updated doesnt give me compile error now, but when I select either box it doesnt sort I get a blank list box I will need to dig further maybe something else now is the problem.

BidList.RowSource = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
    "FROM PropT " & _
    "WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _
    "AND OfficeName= """ & OfficeNameCombo.Column(2)& """ "  & _
    "ORDER BY " & Forms!PropListF!SortBy

Adam Schwanz
10 hours ago

Ambersands are inside of quotes after the both the words column

Adam Schwanz
10 hours ago

Actually maybe I'm lost in all the line breaks too, just make a textbox on the form called status then make
status = bidlist.rowsource
in your code, so you can see what it's actually saying.

Scott Axton
5 hours ago

Brent -
I often find that putting it back into the query builder and letting Access help saves me a lot of headaches sometimes.

Another thing I do is make it work first by putting in known data. Then 1 by 1 switch out the known data with the variables.

Something that helps me is to think of the variables as including the &.  So...  & variable &

"WHERE YEAR(BidDate)=YearCombo.column(3)&"" & _
      above is missing the & before YearCombo.
so & YearCombo.column(3) &

Something else that is helpful for me is to set the sql string to a variable and using Richards StatusBox or even a MsgBox look at what the sql is converting to.  Like this:



     Dim SQL As String



     SQL = "SELECT BidID, JobName, JobCity, EnteredDate ..."    ' Enter the rest of the SQL string here.

    

     MsgBox SQL

   

   ' Once You have it working set the RowSource and remark out the MsgBox



    ' BidList.RowSource = SQL

Scott Axton
4 hours ago

You also need ending quotes after  "ORDER BY " & Forms!PropListF!SortBy
so   "ORDER BY " & Forms!PropListF!SortBy & ""

Try this (I think it works):

SQL = "SELECT BidID, JobName, JobCity, EnteredDate, BidDate, ClientName, PropPrice, OfficeName " & _
          "FROM PropT " & _
          "WHERE YEAR(BidDate)= " & YearCombo.Column(3) & " " & _
          "AND OfficeName= """ & OfficeNameCombo.Column(2) & """ " & _
          "ORDER BY " & Forms!PropListF!SortBy & ""

.

Alex Hedley
3 hours ago

Just to complicate things I usually use Chr(34), the double quote character " instead of double double quotes as sometimes it's easier to see where they should start and end.

Google Drive Upload Images Link
Ibrahim Hasouna
21 hours ago

I though google drive are Evil that don't work well with Access as you said in Access Evil Access stuff, yet you use it. Actually; I can't compact and repair when I but my DB on Google Drive folder, I have to take on my PC, compact and repair it, then get it back on google drive, if I didn't do that the DB goes too large, ... advice please

Richard Rost
20 hours ago

I use (and love) Google Drive and the Backup and Sync. But you should never run an Access database that's INSIDE that folder. Run it a difference non-synch'd drive and back it up to your Google drive. Same goes for compacting it. Copy it to your desktop or somewhere else first.

Size of Images Upload Images Link
Carol Herriot
2 days ago

I would like all of the images I load into the Image Boxes to fill the box e.g. width = 100% and keep aspect-ratio??
"Stretching" the image causes distortion.

Alex Hedley
2 days ago

Are you setting the sizemode?

Carol Herriot
2 days ago

Yes. It is set to zoom. Stretch distorts the image.

Show 6 More Replies...

Richard Rost
2 days ago

Access isn't great at rendering images if you're resizing them. Consider the Image control a "preview" of the full image. If you want the image to look crisp and clean, the only other option then is to size the picture using your image editing software (Photoshop, et al.) and make it the exact size you want.

Carol Herriot
2 days ago

Thank you. That is what I suspected.
The width of my image (Properties Sheet) is 3.45". Would I then edit each image to that width?

Richard Rost
2 days ago

Usually. I'd do one to see what it looks like. That's what Access is measuring the width of the control on the form as if you were to print it out (like in a report). It may not be exact. Trial and error is your best bet here.

Carol Herriot
19 hours ago

Thank you Richard: I am able to resize the images in Paint quite easily for my purposes.

Richard Rost
19 hours ago

Glad it works for you. :) Access really isn't an graphics program. It can display simple images, but if you ask it to render a resized image, it doesn't look fantastic.

Get totals from another table Upload Images Link
David Wilcox
2 days ago

Richard-
Once again, I must come and kneel before the master in search of wisdom. I have a main table that 'counts' the number of records and also 'sums' the total dollars at the bottom of the table. Is there a way to retrieve those two data items and show them in text boxes another form? For example, on my main dashboard form I'd like to be able to show a 'current number of records total' and a 'current contract value total' amount on the main dashboard form. As I mentioned, the table for that main dashboard form already totals those columns as needed. I just need to know 'if' and 'how' I can get those figures to populate text boxes I have set up at the top of my dashboard form so that the users can quickly see how many records and what the total value is currently when they open the database. As always, thanks for being the "Best in the Business" when it comes to teaching the 'unteachable' about Access! Live long and prosper!

Alex Hedley
2 days ago

Look at the DOMAIN Functions, DSUM, DCOUNT etc

Richard Rost
2 days ago

Alex is correct... look at DSum. Start by learning DLookup. It's a close cousin, but easier to understand.

Show 2 More Replies...

Multi Value Combo Box Upload Images Link
Austin Ritner
2 days ago

Just learned about multi value combo boxes. I set one up based on how I was told on the microsoft website. It's control source is a query. Whenever I select multiple values and move to the next item in the form, Access freezes, shuts down, and opens back up. Any ideas?

Richard Rost
2 days ago

No. No. No. Do NOT use multi-value combo boxes (or multi-valued fields). They are on the the Evil Access Stuff list. If you need to select multiple values, use a multi-select list box or a subform with check boxes.

Austin Ritner
2 days ago

Got it.

Access Form to Formatted Excel Upload Images Link
Mark Pierce
2 days ago

I have a request to create an export from a specific record set view in a form to an excel sheet. However they want it formatted to look similar to the Access form. Are there any help videos that goes over this? Export to excel is very straight forward but to a formatted sheet if very different. Read More...

Richard Rost
2 days ago

So you want to take the data as it looks in a standard Access form (single form view) and export to Excel but have the fields and labels all laid out like that in the spreadsheet? Yeah, I'm sure it could be done, but that's a tough one. You could do something simple with a CSV file and just export the field names, values, and blank values for where you want spacing.

Richard Rost
2 days ago

Show 4 More Replies...

AfterUpdate Event Coding Upload Images Link
Mark Hanstad
3 days ago

How would I code the following:

I have a form with two fields -- Purchase Date and Refill date

Also, I have two checkboxes
1. 30 Day Refill Date = yes/no
2. 90 Day Refill Date yes/no Read More...

Adam Schwanz
3 days ago

What do the checkboxes do? So if you bought your prescription 7/1 and then on 7/31 when you came for your 30 day refil you just want to check the 30day refil checkbox and update the refill date? If so just put an after update refilldate = purchasedate + 30. If you are going to continue to push the button more than once, maybe say if refilldate - purchasedate > 30 then refilldate = refilldate + 30 else refilldate = purchasedate + 30

Mark Hanstad
3 days ago

once a prescription is purchased 7/1/21 and the checkbox is checked for a 30 Day Refill Date, I want the Refill date to show 8/1/21.

for a 90 day script of a purchased date of 7/1/21, and the checkbox is checked for a 90 Day Refill Date, I want the Refill date to show 10/1/21.

does that help?

Show 4 More Replies...

Adam Schwanz
2 days ago

Ok yea, so if its just that simple all you need is an after update event on the 30day checkbox that says refilldate = purchasedate+30 then on the 90 day checkbox do refilldate=purchasedate + 90

If you mark the checkbox before hand and then put the purchase date after. You can put an after update event on the purchasedate field that says
If 30daycheckbox = true then
Refilldate=purchasedate+30
Elseif 90daycheckbox = true then
Refilldate=purchasedate+90
End if

You might also want to error check it so you cant have both marked and instead say if 30daycheckbox=true and 90daycheckbox=false kind of thing, then add in if scenario for if both are false or both are true to msgbox something to fix the checkboxes first and exit sub.

Richard Rost
2 days ago

This assumes it's one or the other. Could they get both a 30 day and 90 day refill? :)

Mark Hanstad
2 days ago

Sorry to bother tou again.

I am getting a compile error syntax error

If 30daycheckbox n= true then  (this is the line starting with 30daycheckbox I get the error. Its in Red, it not sure what to do)
  Refilldate=purchasedate+30
Elseif 90daycheckbox = true then  (this is the line starting with 30daycheckbox I get the error. Its in Red, it not sure what to do)
  Refilldate=purchasedate+90
End if

I tried the folwing with the same error. just changed purchasedate to pickup
Private Sub Ctl30DayRefiillDate_AfterUpdate()
If 30DayRefillDate =true then
  Refill Date = Pickup + 30
End If
End Sub

Private Sub Ctl90DayRefiillDate_AfterUpdate()
if 90DayRefillDate = true then
  RefillDate = Pickup + 90
End If
End Sub

Richard Rost
2 days ago

Hi Mark. You've got a bunch of typos in there. Your variable and/or field names shouldn't have spaces in them. I see a couple of them, like "30daycheckbox n" and "Refill Date". You may want to watch my Intro to VBA video before trying to tackle this one.

You may want to go with a simpler, non programming solution. In your RefillDate text box, set the Control Source to this:

=IIF(90DayRefill,PurchaseDate+90,PurchaseDate+30)

That's it. Of course replace "90DayRefill" with whatever the name of your check box is. Basically if the user checks the 90 day box, they will get 90 days, otherwise 30 days.

Sometimes the IIF Function is better for simple things like this, especially for Beginners.

Link databases as separate mod Upload Images Link
Garry Smith
3 days ago

I would like to see a TechHelp video showing how to use separate databases much like modules that can be added at a later date when someone requests added features such as adding a checking account module when they already have the Custome/Contacts database. If the Customer/Contact database is split how can this be accomplished when the user is using Access Runtime?

Richard Rost
2 days ago

That would be tricky. You'd have to shell out to another copy of Access and load a 2nd database file. The data could all be shared to the same back-end.

The way I plan on handling "modules" for my ABCD database is to have them all built into the ACCDB file and then when the user registers their database, they will unlock whatever features they've paid for. The invoicing, mail merge, etc. modules will all be in there, but you only get the functionality you pay for, which is easy to lock down in an ACCDE file.

Pass Value to Parent Form Upload Images Link
Dg Ewing
3 days ago

I was struggling to create code that would pass a value from a subform [IcwID] to the mainform .

One of the attempts used the subject syntax/code which did indeed pass the value from sub to main. Read More...

Scott Axton
3 days ago

Are the pictures in the previous post relevant here?

Typically the form -> subform setup is more of a top down situation. You are producing records in tables in the subform that are related to the parent or main form. I don't recall any teachings by Richard that involve passing data backwards from a subform to the parent form.

If you are getting that error it is because you are likely directing Access to insert a record using the ID field which you can't do if it is an auto number field. That is what causes the duplicate value error.

Scott Axton
3 days ago

To accomplish what you are speaking about, you will more than likely have to have a separate form. One that has your data that is currently in your Subform as the "parent" and relate it to the data that is currently in your parent form as the subform.

Show 8 More Replies...

Richard Rost
2 days ago

What do you mean by "pass a value?" What are you specifically trying to do? Walk us thru the steps.

Dg Ewing
2 days ago

I accomplished what I wanted to do, and that was covered in other post. I have a main form with 5 subforms. The main form gives me the member record (members of the Ewing MDKA [most distant known ancestor] Project--900+) subform 1 provides the DNA kit info, sf 2 provides relationship to me and the amount of shared DNA and #ofSegments, sf3 provides who the MKDA(s) are, sf 4 gives urls to various genealogical sites, and sf5 provides who they are ICW (in common with) from IcwT.

I many times want to click on the Match ID [IcwMemID--foreign field in IcwT = MemID on mf and MemT key/autonumber] to see the member info of the ICW person, I struggled getting my code to work, and finally did after 3 frustrating days. The issue was that I need to reqry, refresh, repopulate the main form rs if I had previously did a search. On a fresh load, everything worked fine, it wouldnt if I had down selected /filtered . see my 1st discussion for all the uglies.

Richard Rost
2 days ago

Gotcha. It's just difficult to envision what's going on without understanding the context of your database.

Dg Ewing
2 days ago

but the me.Parent!MemId=me!IcwID created what I wanted, it populated the mf with the MemID equaling the IcwMemID from the sf, but it also thought that I was trying to add (duplicate) the entire record .  I don't know why it didnt work or how to fix, as that code is pretty simple (at first glance) and my other attempt finally worked
Private Sub IcwMemID_Click()
     Dim strsearch As String
     Dim passvalue As String

     MsgBox [Forms]![A__MDKAMemExtMainF]![A_IcwInputF]!IcwMemID
  'to test if IcwMemID was captured

     passvalue = Me!IcwMemID
' to test if passvalue was set to IcwMemID value

     strsearch = "SELECT * from A_MemberT"

     [Forms]![A__MDKAMemExtMainF].RecordSource = strsearch


     MsgBox passvalue
' to retest after using the [Forms] cmd above

   DoCmd.OpenForm "A__MDKAMemExtMainF", , , "MemID =" & passvalue
'was too tired and frustrated to find out why  the above w "MemID = "& IcwMemID didnt work

End Sub

Dg Ewing
2 days ago

btw, I made up the [Forms]![A__formname].RecordSource = strsearch.....it was the only way I could figure out how to repopulate, reqry (although I dont use a qry--the main form gets data from MemT) or a refrsh.....

This is my only "signature" coding....I am sure that it has been used by many , but I created it without copying ...

Richard Rost
2 days ago

The problem is without seeing your database - or at least screen shots - all of your form and field names are pretty much meaningless and confusing. Read that post that I mentioned earlier: Context. If you can simplify things and express them in terms that I can relate to (customers, orders, etc.) then it makes it much easier to help you. I have no idea what [Forms]![A__MDKAMemExtMainF]![A_IcwInputF]!IcwMemID is.

Dg Ewing
2 days ago

I think it is code that reads the value of IcwMemID, on the sub form A_IcwInputF which is on the main form A__MDKAMemExtMainF.....Well I know it is code that works because the MsgBox returned only the number of IcwMemID.

Wow maybe I have two signature codes...

Dg Ewing
2 days ago

Show Older Comments...
View in Table Format