Linking a popup form to the main form

[dvgef2]

I have used Access primitively for over 10 years and I have a problem that I have never been able to figure out. Since I could never figure it out, I would always end up settling for something less or a work around. I am sure it is a common problem for amateurs and unprofessionals like myself but is probably not a problem for most people in this forum. I don't use code much because I don't understand a lot of it and so most of the data bases I have created are extremely primitive. They serve my purpose as I have always been able to use macros and property values to get what I need. PROBLEM: I have two forms made in Access 2013, a checkbox on the main form will open up the other form as a popup. When the popup form opens, it always starts at record 1 where as the main form I am working off of would be on record 10 or record 20 or whatever. I have never been able to come up with anything that would link the popup form to the main form so that when I start typing in information it is going on the right record. I am sure a subroutine or function is probably what is needed because I have spent weeks trying to find some kind of macro or property setting to do it. I am not sure how much information somebody would need to assist but basically the main form is a work order and when I click on the 'Roofing' check box, a pop up form would open where I would like to type in the type of shingles, amount of shingles needed, contractor and other information. I would have 4 checkboxes on the main form where each would have a different popup form that would open when checked. If any one can be of assistance it would be very appreciative. Thanks in advance!

[pbaldy]

Does this help?

http://www.baldyweb.com/wherecondition.htm

[dvgef2]

Thanks Pbaldy! Initially I was getting a runtime error (1035) using the fields that I wanted to use but then I tried using the 'Autonumber ID' and that seems to work. All those years of working around this problem and it was such an easy fix. Thanks again!

[pbaldy]

Happy to help! Welcome to the site by the way!

[dvgef2]

Thanks Paul, I think it is neat that all the Access masterminds can get together within this site. I however am not one of the masterminds but I am determined. That link you gave me did work for the most part but it does not work with the field I need. I was using the Autonumber "ID" and that works fine with records already created but when a new record is created on the master form, the pop up form comes back with a zero in the 'number data type field' titled "Job Number" even though the right number is on the master form. I tried closing and reopening the main form before the pop up opens with code but that only created more problems. So now I changed that line you gave me to represent the proper field but I get a " Run Time Error 3075, Syntax Error (Missing Operator) in query expression, Job Number =1007 ". The Job Number on the Master Form does = 1007 but again the Run Time error stops everything. This is what I changed the line to and I apologize in advance for the naming convention, I was trying to be smart and label the forms and fields so I know what they do. DoCmd.OpenForm "Work Order subform (Roofing)", , , "Job Number = " & Me.[Job Number] 'Job Number' is the same name for the field on both the master form and the pop up. If you have any idea as to what is going on, I would appreciate your assistance! Thanks again for your reply!

[Missinglinq]

That's because you haven't saved the New Record before going to your Popup Form. To do that, on a line immediately prior to the line opening the Popup Form, use this code:

DoCmd.RunCommand acCmdSaveRecord

Linq ;0)>

[dvgef2]

Thanks Linq, I appreciate your time and response. Any Idea why the run time error keeps popping up?

" Run Time Error 3075, Syntax Error (Missing Operator) in query expression, Job Number =1007 "

That one line of code appears to be proper...maybe it is the unorthodox naming convention it doesn't like! Thanks again!

Dave

[Missinglinq]

The syntax in your code

"Job Number = " & Me.[Job Number]

is only correct if the Field named [Job Number] is defined, in the Table, as a Number Datatype. Fields containing all digits, when not used in math, are frequently defined as a Text Datatype.

If [Job Number] is defined as a Text Datatype, the correct syntax would be

"Job Number = '" & Me.[Job Number] & "'"

Note the placement of the Single Quotes (in red) as they frequently trip up developers, especially newbies.

Linq ;0)>

[June7]

The error message is related to syntax not datatype mismatch. WHERE CONDITION argument must follow SQL syntax. There is space in field name. Enclose in brackets.

"[Job Number] = " & Me.[Job Number]

This is why advise against spaces or special characters/punctuation (underscore is exception) in names. Better would be JobNum or Job_Num.

And as Linq pointed out, if [Job Number] is text field, use apostrophe delimiters.

A non-VBA option is to base the pop-up form on a parameterized query. The filter parameter would be a reference to field on main form. The pop-up will open filtered to only those records that meet the criteria. I don't prefer dynamic parameters in queries but it is an option.

[dvgef2]

Thanks all, June7 is correct because they are both number Data types (Integer). I believe my naming conventions need improving...there are spaces in most of my names. Anyway, Linq was very helpful with the coding for saving the form and once I verified that worked, I went back to the Autonumber data type "ID". Everything works fine now. I think I learned a valuable lesson for future references. Thanks again! Dave