Confusing problem, various error messages

[ReginaF]

Hello Everybody!

So I am lost, confused and at my wits’ end here I am relatively new to Access and I ran into a quite confusing problem.

To start from the beginning: I have a form where I added a button. With this so called "editBtn" I would like to close the current form and go to another, where I can edit the record. And here is where all my problems start. I would like to only see one record on this new form, the one I selected on the previous form. (I am currently watching a Udemy tutorial on this, and it is superb, and it explains how to do all of this in general, however when I try to run it, Access gives me various types of error messages).So the line of code that the debugger highlights for me is the following:

Me.RecordSource = "SELECT qry_ElsoKor.* FROM qry_ElsoKor WHERE ((tblPersonalData.MBszám) =" & Me.OpenArgs & ");"

To explain: I have 2 tables(one is tblPersonalData), that I "sum up with a join" in one querry (gry_ElsoKor). MBszám is my PrimaryKey (in both tables). This Key is stored as a string, normally it is a barcode (like: MB158154), however at times it is only a few digits number, or a name. I tried to set the type of OpenArgs to Variant for this reason (however I also tried String before).

So, onto the main event: whenever I try to run my code, when OpenArgs is a number, I get a Type mismatch error. If OpenArgs stores a Name then the error message is syntax error. If OpenArgs is a barcode then something like parameter specification appears.

So this is my problem... I think I tried everything I am capable of with my limited knowledge. I tried to set OpenArgs as Variant, then tried SCrt(OpenArgs), tried to edit the code above, however I am totally lost.

I would really appreciate any kind of help.
Thank you in advance!

Regina

 

[pbaldy]

Try

Me.RecordSource = "SELECT qry_ElsoKor.* FROM qry_ElsoKor WHERE ((tblPersonalData.[MBszám]) ='" & Me.OpenArgs & "');"

 

[theDBguy]

Hi Regina. There might be an easier way rather than using OpenArgs and modifying the RecordSource. For example:

DoCmd.OpenForm "FormName", , , "FieldName=" & Nz(Me.ControlName)

 

[ReginaF]

Try

Me.RecordSource = "SELECT qry_ElsoKor.* FROM qry_ElsoKor WHERE ((tblPersonalData.[MBszám]) ='" & Me.OpenArgs & "');"

Thank You sooo much! it is working! If it is not too much to ask, can you explain it to me, how did you know this way it would work and why is it working this way? I would love to know.

 

[pbaldy]

The data type of your field is text, and text values require delimiters. Here's a FAQ:

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

 

[pbaldy]

Oh, and I bracketed your field name, though it may not have been necessary. I wasn't sure if the accented letter would be seen as a symbol.

 

[ReginaF]

Oh, and I bracketed your field name, though it may not have been necessary. I wasn't sure if the accented letter would be seen as a symbol.

Thanks! I would have never thought of this
I do not want to abuse your kindness, but can I have one more question? It is not releated to this problem but to the form I am currently working on.

 

[pbaldy]

Sure, here or in a new thread if appropriate.

 

[ReginaF]

Sure, here or in a new thread if appropriate.

So now I would like to add a new button to my form, with this I could create a new record. I tried to use basicly the same commands as with the "editBtn", I set allowAdditions to True, but whenever I click ADD (even when I set close form and Openform)I go to the form where I can modify an existing record (with the record where the cursor is on the original form). Now I am a bit worried that with this I would modify an existing record or completely rewrite.

It would be great if I could 'repurpose' the Editing form for Adding new records if needed, but without losing an existing record.

How can I do that?

 

[pbaldy]

I'm not clear on the situation. If you want the form you're on to add a record, you can use GoToRecord to go to a new record. If the form isn't open, you can use the data mode argument of OpenForm.

 

[NauticalGent]

PMFJI...

Regina, I have a similar situation on one of my forms. The code behind the button is:

Me.DataEnry = True

This saves the current record and opens up a Blank form to add new records - but data entry forms can be confusing to the user in some cases. I have users who get click happy so I intentionally disable the addNew button while the form is in data-entry mode.

I had one user who added 50+ bogus records before he figured out that maybe he should stop clicking it! A little more validation on my part would have prevented this of course...live and learn.

 

[ReginaF]

I'm not clear on the situation. If you want the form you're on to add a record, you can use GoToRecord to go to a new record. If the form isn't open, you can use the data mode argument of OpenForm.

Sorry, maybe my explanation was a bit messy :/ So I have this "main" form, and here i have two buttons: the add and edit. Now if I try to use the add button I allways jump to the record editing 'function'. My code for the add button is the following:

Private Sub addBtn_Click()
Me.AllowAdditions = True
DoCmd.RunCommand acCmdRecordsGoToNew
DoCmd.Close acForm, "frm Search ElsoKor"
DoCmd.OpenForm "frm ElsoKor Edit"
End Sub

I have seen a 'trick' on Udemy where they kind of like set one entry of the recor to blank afer the GoToNew line, to generate a new ID for the record, as they had autonumber PrimaryKey. In my cane I do not know if that is a viable option as my PrimaryKey is barcode that is not an autonumber.
So whenever I try to run the code above I access the edition of an already existing record (not a blank new record)

 

[ReginaF]

I think I fixed it by creating a new form , that is when loading goes to newRecord

 

[pbaldy]

Glad you got it working.