Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 59
  1. #16
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Thanks,

    My original database in Approach is huge and handling statement of accounts, stock system, invoicing, bookings, sales etc. im not designing as I go, just trying to understand how Access works in comparison to Approach as there is no point in me building the rest of the system if I am unable to resolve the first issue I come across.
    I will make the changes you have highlighted above.

    In Approach, when you make a join for relationship purposes then those are automatically used for look-up/query purposes and I think this may be where I am becoming confused.

    I can use those joins but I dont understand how that produces the result of auto-populating the form when I type in the door number and postcode.


    In the UK we dont have zipcodes. The combined door number and postcode unique identify a particular house which is why the door number is separate from the rest of the address.

    Also, even with your changes I am still unable to enter data in the form. I still get 'the recordset is not unpadatble' at the bottom. Any ideas why this is?

    Thanks

  2. #17
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    James in response to your post #12, here's how it can be done with Access:

    Booking Database-davegri-v01.zip

    Click image for larger version. 

Name:	booking.png 
Views:	24 
Size:	27.3 KB 
ID:	41118

    I agree with others that the design is incomplete and missing useful data.

  3. #18
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Thank you, thats partially what I wanted but an automated search without manually having to search.

    In my approach database when you enter the postcode and door number it automatically populates the rest of the details if the record matches.

    I cannot understand how this can be so difficult.

    I tried to create a sample database so which had a number of fruits and colors saved in them. I tried to make it so when i typed 'Apple' or 'Lemon' and tabbed out of the field it populated with the color and I couldn't get that to work either. Surely it cannot be so hard. I cannot help but think I am not explaining myself particularly well and you good people are trying your best to assist.

    I have attached a printed version of one of the forms from my approach database which I am trying to replicate in Access.
    Click image for larger version. 

Name:	Repairs form.jpg 
Views:	23 
Size:	121.4 KB 
ID:	41119

  4. #19
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Thank you, thats partially what I wanted but an automated search without manually having to search.

    In my approach database when you enter the postcode and door number it automatically populates the rest of the details if the record matches.
    Did you try the DB from Post #17?
    You enter the postal code and door no in the heading and click SEARCH. If the entries match an existing record, it will autopopulate the form, showing any existing bookings for that customer in the subform. New bookings can be added there.
    If the postal code and door do not match an existing customer record, a message box will pop up and ask if you want to add a new customer.

  5. #20
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Quote Originally Posted by davegri View Post
    Did you try the DB from Post #17?
    You enter the postal code and door no in the heading and click SEARCH. If the entries match an existing record, it will autopopulate the form, showing any existing bookings for that customer in the subform. New bookings can be added there.
    If the postal code and door do not match an existing customer record, a message box will pop up and ask if you want to add a new customer.
    Hi,

    I did try that and it works well but it's not automatic.
    You have to manually click search. Also, when you then enter a customers address it then doesn't add this to the customer database.

    And so yes, that's a little like what I wanted but there's still some elements which don't work as I am trying to achieve.

    Regards

  6. #21
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Updated DB:
    Booking Database-davegri-v02.zip


    I did try that and it works well but it's not automatic.
    I removed the search button. The search will now execute if both textboxes contain data when you exit the textboxes.
    Also, when you then enter a customers address it then doesn't add this to the customer database.
    I'm unable to duplicate this problem, as Access is pretty adamant about saving records.

  7. #22
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi James

    The Search By DoorNr/PostCode is very easy.

    See the attached example
    Attached Files Attached Files

  8. #23
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Thanks for your responses but you've both got the same issue. You both have separate search boxes for the postcode on door number.

    Look at the form that I posted above. The first tab is 'postcode' I enter M22 2FB. The 2nd tab is 'door no' and I enter 24. when I tab out of that field into tab 3 (which is 'title') it instantly populates the surname, address etc etc.

    If the postcode and door number doesnt match anything then it will tab into that field, I enter it and it saves it into the customer database so that the next time I type those in it can recall it.

    Maybe Access cannot autofill like this but it seems a simple task - that said, I cant work out how to do it! In lotus it was as simple as dragging and dropping the field from the relating database onto the form.


    This guy kind of does it what im looking for here at around 40 seconds...

    https://www.youtube.com/watch?v=bzAWn_YybxY

    But I want to be able to type the details - in my case a postcode - instead of a drop down box. I also need it to be based on two fields and not just the single one - the second being the door number. But you see how he doesnt have to have a special section for searching this data in the form.
    But his example is so complicated that I cant really understand how he does it and ive watched many videos on how to do it and they all have complicated examples which I cant work out.

    Thanks

  9. #24
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Booking Database.zipEvening,

    IVe attached a more upto date version which may help you visualize what im trying to achieve.

    As you can seem the first fields are 'Postcode' and 'Door No'. Once I typed into this and tab into the 3rd field the rest of the address and phone numbers should populate with information from the customer info table.

    Also, another strange issue I cant seem to fathom, if you see the Staff Init field - this should give me a drop down list from the 'Staff List' Table but I just get those strange location indicators as opposed to the staff names. Why is this?

    Thank you all again.
    Attached Files Attached Files

  10. #25
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Access can't use bound controls for searching like that. Bound controls are attached to table fields. If you change the value of the bound control, it updates the table with that value.
    You are not going to be able to duplicate the GUI of Approach with an Access GUI, and I doubt that you could duplicate an Access GUI in Approach. They both tackle the GUI in different ways.
    You've been provided a couple of traditional Access techniques here that duplicate the functionality that you require, using Access GUI.

  11. #26
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Thanks for your reply although I dont agree.

    If you look at the youtube video I posted earlier you can see someone doing something very similar to what I am trying to do. He is only doing it with a single field however.

    This guy kind of does it what im looking for here at around 40 seconds...

    https://www.youtube.com/watch?v=bzAWn_YybxY

    Its really not a huge ask of a database system which leads me to think I am not explaining myself very well.

    If you just see how he selects the details based on a drop down box then I am trying to do similar but instead of a drop down box I am trying to type it - same thing however, drop down box or otherwise.

    Regards

  12. #27
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  13. #28
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    James

    Would the attached be close enough.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  14. #29
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi James
    I've only just picked up on this thread and see you are also UK based.
    For info I have several UK postcode applications both free and commercial which may be of help (see below)

    Many UK addresses don't have a house number or have text equivalents e.g 24A or Flat 14.
    How would you manage situations like that?

    I see you are already receiving expert help from several forum members so I'll largely refrain from adding further code suggestions at this point.
    However, I've looked at both your database from post #24 and Bob's from post #28.

    Just wondering whether you could use a modified version of Bob's approach but using a combo for postcode and adding a house name field as well as house number (or combining both using a text field for name or number).
    If you use a combo for postcode, you can add NotInList event code to look up addresses for the entered postcode.
    For this you may be able to use/adapt my Postcode Address Lookup app http://www.mendipdatasystems.co.uk/u...kup/4594767232
    The app is free if you want to try it (with source code available at a low cost).
    It is a cut down version of my commercial app available at http://www.mendipdatasystems.co.uk/u...der/4594138311
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #30
    James_liv is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    28
    Quote Originally Posted by Bob Fitz View Post
    James

    Would the attached be close enough.
    HI Bob,

    Thanks for that. Thats closer to what I am looking for but is there a way to have the address details listed in the form as opposed to a table within the form? If you see my database in post 24 you can see the layout.
    Also, without the drop down box.

    The thing im struggling with is surly there must be lots of Access databases where people type a book number and it populates the book name in the form? Or student number populates the student name and address, or Employee ID etc etc etc. It must be a common requirement for a database to self-populate certain fields as you type.

    Thanks

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple Sub-Form Lookup Table Updating Issue
    By tmptplayer in forum Forms
    Replies: 8
    Last Post: 08-09-2017, 07:45 PM
  2. simple snytax (lookup most recent record)
    By markjkubicki in forum Programming
    Replies: 9
    Last Post: 06-20-2017, 02:50 PM
  3. Newbie, seems simple to lookup value?!
    By ayce123 in forum Queries
    Replies: 3
    Last Post: 03-13-2011, 12:22 PM
  4. Lookup Form (should be simple)
    By joshlee in forum Forms
    Replies: 3
    Last Post: 05-06-2009, 12:04 PM
  5. Simple record lookup?
    By Transeau in forum Access
    Replies: 0
    Last Post: 01-18-2006, 10:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums